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

That's interesting - will keep it in mind. I don't have a need for it at present.

Kath
  ----- Original Message ----- 
  From: John W. Colby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, October 13, 2004 2:27 PM
  Subject: RE: [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



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