Definition: Filters – functions

 

Definition: Filters – functions that can be called to set a variable value, then called again to retrieve that variable value.

 

I called these functions filters because I used them initially in the where clause of queries.  As you are probably aware, in Access you cannot directly reference a global variable in SQL or a query, however you can reference a function.  Thus you can do one of several things to allow a program to set a “global variable” and then allow a query to reference that global variable.  The first thing you can do is build a module with variables and a

matching function to retrieve the variable:

 

Dim intMyVar as integer   Function MyVar() as integer             MyVar = intMyVar End function

 

 

 

 

 

 

 

 

This method works just fine, but it has the disadvantage that the variable and the function are separate.  The next method uses static variables to move the variable inside the function.

 

Function MyVar(optional varMyVar as Variant) as variant Static lvarMyVar as variant               If IsMissing(varMyVar) then                         MyVar = lvarMyVar             Else                              lvarMyVar = varMyVar                        endif     End function

 

 

 

 

 

 

 

 

 

 

 

 

This method has you pass in a variable to an optional parameter if you are setting the filter value, or not pass in the optional parameter if you want to read out a previously set value.

 

This method has the advantage that the value being stored and returned is encapsulated in the function such that the function can be cut and pasted all by itself and used elsewhere. 

 

The downside is that the value must be a variant in order to use IsMissing() on the optional parameter passed in.  However IsMissing only works on variants and there is no other way to reliably determine if the user intends a read without going to a second Boolean parameter that specifies that this is a read or a write, so the disadvantage is outweighed by the ability to use the Optional keyword and using the function for reading and writing the same variable.

 

Both of the previous methods have the disadvantage that you have to build a new function for each filter since each filter function has a specific name and is used to read / write a value for a specific variable. 

 

I used this second method for awhile until I had a dozen or so functions and started questioning the idea of creating new functions for each one.  I wanted a way where I could create these filter “functions” on the fly as I needed them instead of stopping what I was doing, going out to a module, cutting and pasting and renaming a previous function to a new function name.  The method I came up with was to use a collection to hold the values to be stored / retrieved, with the key being the “name of the filter”.

 

Collections use a syntax:

 

MyCol.Add MyValue [, MyKeyName]

 

In other words, the add method requires a value to be stored in the collection, but it may or may not be fed a “key” value, used to index into the collection later to retrieve a specific value.  If used, the key must be a string.  Thus:

 

MyCol.Add 199, “MyIQ”

 

Would store the value 199 into the collection MyFltr, but it would do so such that I could later retrieve the value by asking for the item “MyIQ”.

 

The entire code follows:

 

Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As Variant On Error GoTo Err_Fltr Static mcolFilter As Collection Dim intCnt As Integer     '     'Checks for the collection initialized     '     On Error Resume Next     intCnt = mcolFilter.Count     If Err <> 0 Then         ‘If not initialized yet, do so now         Set mcolFilter = New Collection     End If     '     'We have a valid collection, next check for a value passed in     '     If IsMissing(lvarValue) Then         '         'No value was passed in so check in the collection         'using the variable name (key)         '         On Error Resume Next         Fltr = mcolFilter(lstrName)         If Err <> 0 Then             '             'There was no value in the collection under that name (key)             'so just return a null             '             Fltr = Null         End If     Else         '         'A value was passed in so         '         On Error Resume Next         '         'Remove any value already in the collection under that name (key)         '(if any)         '         mcolFilter.Remove lstrName         '         'And then add it back in to the collection         '         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

 

The code is commented in line.  There is a collection which has to be initialized the first pass through.  To check whether the collection is initialized you try to access a property of the collection – the count.  If that throws an error then you have to initialize the collection.

 

You then check whether the value passed in is empty.  If nothing is passed in then IsMissing will be true.  That means that the function is trying to retrieve a value stored previously so you use the “variable name” passed in to index into the collection.  If that works then a value was previously stored using that variable name and you simply return it.  It that fails, then no value was stored using that variable name and you return a null value.

 

If the value passed in is not empty, then you attempt to delete out any value already in the collection under that variable name.  You then store the value passed in using the variable name as the key, and finally return the value passed in.

 

As you can see, this allows a single function name to be used to store many different variables. 

 

Fltr “MyIQ”, 199

?fltr(“MyIQ”)

Fltr “MyAge”, 129

?Fltr(“MyAge)

Fltr “My Birthdate”, #1/1/1847#

Fltr “My Name”, “Jane the Ingenious”

 

Using this method you now have a single function name that can be called from anywhere to store a variable with an associated name.  The variable can then be retrieved later by calling the same function passing in only the name of the variable.  Notice that the function can store and retrieve any datatype since it uses variants to accept and return the value and internally it uses variants for storage.

 

This is an extremely powerful technique.  It is reasonably fast, though certainly not as fast as simply accessing a global variable directly.  One thing you must be careful of though is to ensure that you pass control.value and not control if you are going to use the fltr function to store the value of a control.

 

One technique common in Access is to directly access a control in the where clause of a query.  The syntax is something like Where SomeField = MyForm!MyControl.  This can get pretty deep, particularly in cases where the control is buried down in a subform.  The syntax quickly turns into MyForm!MySubform.Form!MyControl etc. 

 

It is often convenient to pass the control value in to a filter function, then reference that in the query.  For example, in OnCurrent of the subform you might do something like fltr “MySFrm-MyTxtBox”, MyTxtBox.  In the query you now use WHERE Fltr(“MySFrm-MyTxtBox”) to get the value back.

 

The problem with using this syntax is that because the fltr function accepts a variant, unless you use the MyTextbox.value syntax a pointer to the actual combo is passed in, not the value of the combo at that moment.  When you ask for the value back again, what you get back will be the value of the combo, but that value may have changed.  In other words, it may not contain the value in the combo at the time the filter function was called – which can be very confusing.

 

The correct syntax in this case is to just use the .Value property of the control so that a “pass by value” occurs instead of a “pass by reference”.  Fltr “MySFrm-MyTxtBox”, MyTxtBox.Value passes in the actual value of the control instead of the control itself and you will not have any unexpected problems.

 

The above information can be considered a unit and if you are already confused or just need to absorb this you should stop here.  The following section discusses how to use a Fltr function to pass pointers to actual objects such as controls, forms, classes etc.  If you understand the above discussion, what follows is simply an expansion on that discussion and should not be particularly difficult.

 

You can use the “problem” of passing objects to your advantage.  Suppose for example that you need to pass a control to an opening form.  That form needs to do something and place a value back into the control.  By intentionally using the Syntax fltr “MyProcess”, MyForm!MyControl , you are making the pointer to the actual control available to another part of the program. 

 

You could place a pointer to a control into the filter, then open another form (a calendar form for example).  The calendar form opens and sets a local control variable = fltr(“MyProcess”) .  It now has a local pointer to the control that it needs to update back in some other form.  Of course, after performing the update you need to release the pointer to the control back on the calling form.  Alternately you could just use the object “in place” using the syntax Fltr(“MyCltName”).Value.  Any property of the object can be directly referenced using that syntax.

 

In order to do this however we need to modify the filter function slightly, since in order to return a pointer to an object the function needs to use a SET statement in the code that returns the stored value.

 

' 'The following code expands upon the basic filter function above to allow storing and 'returning objects of any kind - controls, forms, classes etc. ' 'The code takes advantage of the fact that a variant can store a pointer to an object.  In 'order to determine if the thing stored is an object or a normal variable, a SET statement 'is performed first and the err object is checked.  If the thing stored is not an error then the 'Err object will not be zero, in which case we treat the thing stored as a simple variable. ' Public Function FltrCtl(lstrName As String, Optional lvarValue As Variant) As Variant On Error GoTo Err_Fltr Static mcolFilter As Collection Dim intCnt As Integer     '     'Checks for the collection initialized     '     On Error Resume Next     intCnt = mcolFilter.Count     If Err <> 0 Then         'if the collection not initialized yet, do so now         Set mcolFilter = New Collection     End If     '     'We have a valid collection, next check for a value passed in     '     If IsMissing(lvarValue) Then         '         'No value was passed in so check in the collection         'using the variable name (key)         '         'We are going to pass back the value.  Determine if it is an object         'by using the set statement and checking for an error         '         On Error Resume Next         Set Fltr = mcolFilter(lstrName)         If Err <> 0 Then 'if the Err obj is <> zero then it was not an object             Err.Clear             Fltr = mcolFilter(lstrName)             If Err <> 0 Then                  '                 'There was no value in the collection under that name (key)                 'so just return a null                 '                 Fltr = Null             End If         End If     Else         '         'A value was passed in so         '         On Error Resume Next         '         'Remove any value already in the collection under that name (key)         '(if any)         '         mcolFilter.Remove lstrName         Err.Clear         '         'And then add it back in to the collection         '         mcolFilter.Add lvarValue, lstrName         '         'We are going to pass back the value.  Determine if it is an object         'by using the set statement and checking for an error         '         Set Fltr = lvarValue         If Err <> 0 Then        'if the Err obj is <> zero then it was not an object             Fltr = lvarValue    'so just return the value as a variable         End If     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

 

The function above is identical to the first filter function except that when passing back the value it checks whether the “thing” passed back was an object.  In order to actually use a pointer stored by this function we need to use the SET syntax when retrieving the value.

 

Set MyCtl = Fltr(“MyCtlName”)

 

If you try to use MyCtl = Fltr(“MyCtlName”) you will get a run time error.

 

IN this article you have learned how to use a collection to store values that you need to pass to other areas of your programs, or to use in the QBE window in aliases or where clauses.  By using a single function to both store and retrieve the values you can build the functionality of global variables, with the advantage of not having to declare them at design time and a secondary advantage that they can be accessed by queries in Access where normal global variables cannot.  Embedding the storage collection in a static variable inside the function makes the function “cut and paste”, with the storage collection traveling with the function.

 

You can pass normal variables or even pointers to objects.  The values of objects on a control can be made available to queries or even code in other forms or modules.  By passing pointers to an actual control on a form, code in other forms or modules can even directly update the value of the passed control.

 

Used judiciously, a Fltr function can make cumbersome inter-process communication very simple.

 

Author John Colby