John W. Colby
jwcolby at colbyconsulting.com
Fri Jan 7 23:11:25 CST 2005
Anna,
I am honored that you are honored... ;-)
Using a hidden form for passing parameters does work - in fact in my youth I
used that method on occasion - but is clumsy. If the form is ever closed,
the parameters are not available. Adding more filter parameters requires
modifying the form etc.
Using a filter function, particularly one based on a collection, allows you
to add filters whenever and wherever you need them, anywhere in code. They
are reasonably fast, no additional code is ever needed, and they are just
darned convenient.
Enjoy,
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 11:51 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Send Filtered Report
Thanks John. I got both of them. It is getting late and I will have to
look more at this in the morning. I am honored to have a response from
you. I love this list and appreciate all the sharing that goes on. I
unsubscribed in July because I was traveling. But I am back. Thanks again.
Anna
At 10:25 PM 1/7/2005, you wrote:
>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