[AccessD] When a query refers to a control on a form which is not open - how to supply a default?

Bill,

I use a function I call "fltr()" to do this.  Fltr is a function that I can SET to a value with one 
call and RETRIEVE a value with another call.  I have removed error handling to make it easier to see 
how it works.

The concept is that fltr has TWO parameters.  The first parameter is the NAME of the filter to be 
set or retrieved.  The second parameter is OPTIONAL and is the VALUE of the filter to be SET.

Filters are stored internally to the function in a static collection, so this one function can store 
thousands of these filters.

So...

If you call

	Fltr "frmExciterRecID", [Forms]![frmExciterIndividualEntry]![RecordID]

you SET the VALUE [Forms]![frmExciterIndividualEntry]![RecordID] into the fltr "frmExciterRecID"

If you call fltr("frmExciterRecID") you RETRIEVE or READ fltr "frmExciterRecID"

So your code can now SET a filter wherever you need one, typically in code in the OnOpen or 
OnCurrent, and the value can be read back out anywhere you want, but specifically in a query.

This allows you to MANUALLY SET a filter value, perhaps in the debug window, and then open the 
query.  Since you set the filter, the query uses the filter and life is good.

How it works:

The very first time the function is called this code sets up the static collection which holds the 
filter values.

Static mcolFilter As Collection

     If mcolFilter Is Nothing Then
         Set mcolFilter = New Collection
     End If

The following code handles the case where you are just reading values back out (not SETTING the filter)

     If IsMissing(lvarValue) Then
         On Error Resume Next
         Fltr = mcolFilter(lstrName)
         If Err <> 0 Then
             Fltr = Null
         End If

The following code handles a value being passed IN and if so stores it in the collection:

     Else
         On Error Resume Next
         mcolFilter.Remove lstrName
         mcolFilter.Add lvarValue, lstrName
         Fltr = lvarValue
     End If

Notice that we ALWAYS delete the old value and store the new value passed in.

The following is the entire filter function

Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As Variant
Static mcolFilter As Collection

     'First pass set the collection up
     If mcolFilter Is Nothing Then
         Set mcolFilter = New Collection
     End If

     'lvarValue is an optional parameter.
     'if it is missing then we are READING the filter
     If IsMissing(lvarValue) Then
         On Error Resume Next
         Fltr = mcolFilter(lstrName)
         If Err <> 0 Then
             Fltr = Null
         End If
     'if it is not missing then we are WRITING a filter value
     Else
         On Error Resume Next
         mcolFilter.Remove lstrName
         mcolFilter.Add lvarValue, lstrName
         Fltr = lvarValue
     End If
End Function



John W. Colby
www.ColbyConsulting.com


Bill Benson (vbacreations) wrote:
> SELECT TblCIB.RecordID, TblCIB.ShopOrder, TblCIB.ItemNumber FROM TblCIB
> WHERE (((TblCIB.RecordID)=[Forms]![frmExciterIndividualEntry]![RecordID]))
> 
> The above sql is causing the user to be prompted for a value of
> [Forms]![frmExciterIndividualEntry]![RecordID] when that form is not open
> and/or the control not populated. I would like that when there is no form or
> control available at runtime, the query just assumes a 0 or "" [default] so
> that my users are not prompted. Is there some simple [single expression] way
> to do this short of the workaround I show below, which seems like overkill??