[AccessD] Help with syntax please....OpenRecordset

BTW, the concept of the "static stored value" can be made even more useful
if you use a collection to store the value, keyed on the name of the
variable.  In that way you can use a single function to set / retrieve a
multitude of different stored values.

Option Compare Database
Option Explicit

Private mcolFilter As Collection
Private mblnFltrInitialized As Boolean
Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
Variant
On Error GoTo Err_Fltr
    'This sets up the collection if it isn't already initialized
    If mblnFltrInitialized = False Then
        Set mcolFilter = New Collection
        mblnFltrInitialized = True        
    End If

    If IsMissing(lvarValue) Then
        'This path is used to retrieve a value from the collection
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If Err <> 0 Then
            Fltr = Null
        End If
    Else
        'This path is used to store a variable on the collection
        On Error Resume Next
        mcolFilter.Remove lstrName
        mcolFilter.Add lvarValue, lstrName
        Fltr = lvarValue
    End If
Exit_Fltr:
Exit Function
Err_Fltr:
        MsgBox Err.Description, , "Error in Function basFltrFunctions.Fltr"
        Resume Exit_Fltr
    Resume 0    '.FOR TROUBLESHOOTING
End Function

One function can now be called with a "variable name" and a value to store a
value into a "variable".  

	fltr MyValue, "MyVarName"

MyValue can be anything at all, string numeric etc.

If the variable name already exists, then it is removed and replaced with
the new value.  This effectively stores the value passed in even if there
was already a value in the collection keyed on that name.

To retrieve a value, just call the function passing in the name of the
"variable" you want the value of.  If there is no such variable name a null
will be returned.

	MyVar = fltr("MyVarName")

I don't remember right off hand whether a control or form can be passed in
to a variant though I believe it can.  Thus you need to be careful that you
either do not pass in pointers to objects or that you make sure you clean
out the collection before terminating your program.  If there are no
objects, it doesn't matter, i.e. the collection and its values can be
cleaned up by VB as the app closes.  If there are pointers to objects in
there however VB may not correctly clean up the collection and the program
may hang instead of exiting.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kath Pelletti
Sent: Tuesday, October 12, 2004 11:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Help with syntax please....OpenRecordset


Done, done and done. Thanks all for your help. I had no idea that a form
criterion parameter couldn't be used in that way and could have wasted even
more time. 

Kath
  ----- Original Message ----- 
  From: Stuart McLachlan 
  To: Access Developers discussion and problemsolving 
  Sent: Wednesday, October 13, 2004 1:00 PM
  Subject: Re: [AccessD] Help with syntax please....OpenRecordset


  On 13 Oct 2004 at 11:46, Kath Pelletti wrote:

  > I am trying to use a saved query for my recordset source....but keep
getting an error 'Too few parameters - expected 1' on the line 'Set rs = '
  > After many hours, I can't fix it so would appreciate the help.....
  > 

  The problem is in the query "QryWebLetter_ReadRecipientsEmai". You are 
  probably using a reference a control on a form as a criterion/paramter for

  your query. You can't do that when you call the query as a recordset.

  My usual workaround is to create a Static function called StoredValue() to

  store the parameter, change the criterion in the query to "=StoredValue()"

  and set the value of the function before calling the query  with 
  "retval = StoredValue(Forms!myForm.myControl)" or just
  "StoredValue Forms!myForm.myControl"








  -- 
  Stuart


  -- 
  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com