William Hindman
dejpolsys at hotmail.com
Sat Jan 8 02:02:11 CST 2005
"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
----- Original Message -----
From: "John W. Colby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Friday, January 07, 2005 11:25 PM
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
>