[AccessD] Send Filtered Report

Thanks.  Filters are very cool.  I used to do a lot of queries where a
control on the form was referenced in the where clause, but I got in the
habit of using these "filters" instead.  Doing so allows me to design the
query without the form even open.  Or I can use the form that sets the
filter (some combo value or text box value etc), then go to the debug window
and see what the filter value is etc.  It just makes debugging an
application so much more flexible.  I can manually set filters in the debug
window to see what values the query will display etc.

I also oftentimes use these to pass values in to controls on a report.
Sometimes you want something displayed in the header of a report that just
isn't found in the data for the report.  Buy setting the value into a
filter, then setting the control on the form to pull the data from the
filter you can be very flexible.  Again you can work with the report without
any form open to pass the values in to the report.  

The more you use this the more places you discover to use it.  You can use
them similar to a global value, where a process needs to make a value or a
status available for other processes.  The process creates a filter and
stores its value, then any other process can examine that value.  I'm quite
sure that idea has some of the more rigorous structured programmers spitting
nails but ... 

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 Jim Lawrence
Sent: Saturday, January 08, 2005 10:34 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Send Filtered Report


John: Looks very interesting!

Good use of collections...sort of like a flexible TYPE structure?... (A
favourite instrument of mine.)... very cool! :-)

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Friday, January 07, 2005 8:26 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Send Filtered Report

Sorry, my last post got mangled.  Let's see if this is any better.

I prefer to use what I call filters:

You set a filter by the syntax :
	
Fltr MyFltrName, MyFltrValue

You then retrieve (print to the debug window) the filter using the syntax:

Debug.print Fltr (MyFltrName)

The code for doing this is actually a little module.  The idea is that you
set up a collection to hold filter values keyed on filter name.  Adding the
value into the collection stores the value for later retrieval from the
collection.  Thus a single collection can hold as many filters as you need.
The code looks like:

(cut and paste the following into a module.  Fix any problems cause by line
wrap.)

Option Compare Database
Option Explicit
Private mcolFilter As Collection
Private blnFltrInitialized As Boolean
'
'The lvarValue is optional, and the presence of the lvarValue changes the
operation from a retrieve to a store. 
' 
'i.e. if the lvarvalue is passed in, then we are storing a value.  
'If the lvarValue is omitted, then we are retrieving the value already
stored. ' 
'

Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
Variant 
On Error GoTo Err_Fltr
    '
    'The first time through, set up (initialize) the collection
    '
    If Not blnFltrInitialized Then
        Set mcolFilter = New Collection
        blnFltrInitialized = True
    End If
    '
    'If the lvarValue parameter is missing, then we are doing a retrieve
    '
    If IsMissing(lvarValue) Then
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If Err <> 0 Then
            Fltr = Null
        End If
    Else
	  '
	  'Else we are doing a store
        'So just remove anything already stored under that lstrName 
        'and save the new value under that lstrName 
        '
        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

Calling the function the first time initializes the collection, and sets the
Boolean that the initialization has been done.  It also stores the value
lvarValue into the collection using lstrName as the key. You can call the
function as many times as you wish storing values.  For example:

Fltr "MyName", "John W. Colby"
Fltr "MyCompany", "Colby Consulting"
Fltr "MyWebAddr", "www.colbyconsulting.com"
Fltr "MyBirthday", #1/1/1909#
Fltr "MyAge", 105

You can then later retrieve any value you wish, once or a million times
simply by calling the same function but leaving off the VALUE:

Debug.Print Fltr("MyBirthday") - returns #1/1/1909
Debug.Print Fltr("MyAge") - returns 105
Debug.Print Fltr("MyCompany") - returns "Colby Consulting"

Etc.

Notice that if you call the function with the same filter name but a
different value, the code:

        On Error Resume Next
        mcolFilter.Remove lstrName
        mcolFilter.Add lvarValue, lstrName

Removes whatever may be in the collection keyed on the lstrName and adds the
new value supplied, effectively "erasing" the filter value and saving the
new value.

Notice that you can pass anything in to the filter, even pointers to forms
or controls, and you NEED TO BE CAREFUL.  Passing cboState actually passes
the pointer to state combo in to the filter.  Passing cboState.value passes
the value of the combo in to the filter.  This is a not so subtle
difference.  The value is a "by value" thing, where the actual value is
passed in and retrieved back out.  The pointer is a "by reference" thing and
if the combo changes after you pass a pointer to it in to the filter, the
apparent "value" that you will see by getting it back will change.  On the
other hand, this can be useful if you need to pass a control in to a form
that is opening, for example a form does something and places the value back
into a control on another form.

This filter function is a very useful construct and is an extension of
specific named filters used by others on this list.  It is easy to use, and
can be useful in a wide variety of applications, from filtering queries for
forms or reports, to passing in values to be displayed on a form or report
etc.

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.

John W. Colby
www.ColbyConsulting.com 

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


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 Anna Hopkins
Sent: Friday, January 07, 2005 10:36 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Send Filtered Report


Thanks.

I've never used hidden forms but I can see how that could work.



-- 
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