[AccessD] Filters - was filtered reports

John W. Colby jwcolby at colbyconsulting.com
Sat Jan 8 09:54:34 CST 2005


William,

The class approach adds a level of indirection and so I do not recommend it
unless you specifically need it.

The class approach simply embeds the collection, the Boolean and the
function in a class.  That really all there is to it.  Because the whole
thing is encapsulated you can now create an instance of filters for purpose
a, an instance for purpose b etc.  Perhaps you have a module, with related
forms that need to be able to pass parameters back and forth.  The typical
method of doing this is to start adding global variables, add a new variable
every time you need to pass some other thing.  With a class you create an
instance and all the objects of that functional area can pass stuff back and
forth using the class instance that is dedicated to their use.

The class would then look something like:

'###########################################################################
##
Option Compare Database
Option Explicit
'
'The collection to hold the filter values
'
Private mcolFilter As Collection
'
'Set up the collection 
'
Private Sub Class_Initialize()
    Set mcolFilter = New Collection
End Sub
'
'Tear down the collection
'
Private Sub Class_Terminate()
    Set mcolFilter = Nothing
End Sub
'
'Get a pointer to the collection
'
Public Property Get pcolFilter() As Collection
    Set pcolFilter = mcolFilter
End Property
'
'The actual filter function
'
Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
Variant
On Error GoTo Err_Fltr
    If IsMissing(lvarValue) Then
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If Err <> 0 Then
            Fltr = Null
        End If
    Else
        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 clsFltr.Fltr"
        Resume Exit_Fltr
    Resume 0    '.FOR TROUBLESHOOTING
End Function
'###########################################################################
##
The class is pretty simple, an Initialize function that sets the collection,
a Terminate function that cleans up, a property to return the entire
collection, and the Fltr function already explained in the previous email.
No surprises here.


A module to test it:

'###########################################################################
##
Option Compare Database
Option Explicit

Private mclsFltrSet1 As clsFltr
'
'Method One for using Filter classes
'
Function cFS1() As clsFltr
Dim intCnt As Integer
On Error Resume Next
    '
    'This line is used to test if the class is initialized yet
    'You will get an error if the class is not initialized
    '
    intCnt = mclsFltrSet1.pcolFilter.Count
    If Err <> 0 Then
        '
        'If you get an error, then initialize the class
        '
        Set mclsFltrSet1 = New clsFltr
    End If
    '
    'Nolw we know that we have a valid class, get the pointer to it
    '
    Set cFS1 = mclsFltrSet1
End Function

Function testMethod1()
    cFS1.Fltr "MyName", "John W. Colby"
    Debug.Print cFS1.Fltr("MyName")
End Function

'
'Method Two for using filter classes
'
Function FltrWrapper(lstrName As String, Optional lvarValue As Variant) As
Variant
    '
    'If the lvarValue is missing we are retrieving a previously set filter
    '
    If IsMissing(lvarValue) Then
        FltrWrapper = cFS1.Fltr(lstrName)
    Else
	  'Else we are setting a filter value
        cFS1.Fltr lstrName, lvarValue
    End If
End Function
'###########################################################################
##

This module sets up a private variable to hold a pointer to the class.  What
we are going to do is create a function that tests for a class instance and
instantiates the class if it hasn't already done so (the first time).  We
test for a valid instance by trying to get the count of the collection
inside the class.  If that fails then the collection is not initialized, and
by inference the class itself is not instantiated.  

Once we know the class is instantiated we simply return a pointer to the
class.  TestMethod1 then calls the cfs1.filter method, passing in the name
of the filter we want to set up, and the value.  At a later time we then
call the same method passing in just the name of the filter and retrieve the
value.

For many purposes that is enough.  Unfortunately Access queries cannot
reference properties of a class so you must wrap a call to one of the class
instances in a function which a query can then use in a where clause,
aliased field etc.  FltrWrapper does exactly that.  Notice that the function
takes exactly the same parameters as the core filter function buried down in
the fltr class.  This function must determine whether we are setting a value
or retrieving a value and act appropriately.

Unfortunately the whole concept of a class is to have multiple instances,
and this method of dimming an explicit variable for each class instance soon
becomes an issue since you now need a function to return a pointer to each
class instance.  The next conceptual level is a class (I call them
"supervisor" classes) that contains a collection of the filter classes.
Doing it this way allows us to set up a single supervisor class, and then it
can hold as many filter classes as desired.  Essentially you take everything
in this test module and throw it in a class just like we took the filter
module and threw it in a class.  The supervisor then needs a simple module
to initialize it's single variable, and a method to return a pointer to that
class instance.  I will put all of that in another email so that the
discussion doesn't get too long.

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 William Hindman
Sent: Saturday, January 08, 2005 3:02 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Send Filtered Report


"I have also migrated this to a class so that I can set up "sets" of 
filters,
each set in a class instance - but that is a subject for another day." JC

..oh no you don't ...you tease, you please, eh :)

..I'd really like to see the class ...I've always used the hidden form 
myself and I'm impressed by the simplicity of this approach ...so fess up 
already ...if I'm going to implement this I'd rather go whole hog.

William Hindman






More information about the AccessD mailing list