John W. Colby
jwcolby at colbyconsulting.com
Fri Jan 7 22:25:37 CST 2005
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.
More information about the AccessD mailing list