This month we discuss Fancy Filtering Techniques for Queries on Steroids. Before we're done, we will have shown you three ways to return a filtered query. If you want to play with the code for this article, download it here.
The first method is the one you read about in chapter 3 of Access For Dummies, and while it does work, it's incredibly tacky, forcing the user to type in the filter value. The second method filters a query based on some value, which resides on an open form. This method is much more elegant, but works only when you cross your fingers. Ok, that's not completely true, but it seems that way sometimes.
The third method is what I call The Fancy Filtering Technique and it was developed in order to meet these requirements:
- Where possible, users should NOT have to type in the filter value
- The filter should always work, even when a particular form isn't loaded
- The filter should be smart
- lookup the filter parameter where possible
- substitute a default where reasonable
- ask the user to select where necessary
- provide custom dialog box where reasonable
- Remember user selections for subsequent requests
- Log errors and even use statistics if you're anal
Queries built with straw
Yes, the three little Piggys built their queries. The first little piggy read about parameter queries and decided that was pretty slick and it was so simple to use. Here's how it works:
- Create a new query and add your tables and field
- Add a prompt in square brackets to the criteria row of the filter column
Very simple approach and actually quite flexible. The SQL for such a query, shown below, illustrates how this approach may even be used with wildcard matching. If you need a quick-and-dirty way to deliver a filtered query, this will work. However, the dialog box for supplying the filter criteria is free text and users may type whatever they want.
The second little Piggy recognized that sometimes users can't even supply the required parameter, as in the case where you might pass a record ID to a report. Users don't think in terms of unique IDs and getting them to locate it on the form and type it into a parameter box is asking for trouble.
Accordingly, the following technique was developed whereby the query could "look up" the ID from the open form. We do this in VBA code all the time, reference the value of a control, and the syntax looks like this:
SELECT * FROM authors WHERE [au_id] = Forms!frmDemo!au_id
This is elegant, because it's invisible to the user. You could place a report button on the form that opens a report filtered for the current record. The problem is, what happens when someone opens that report when the form is NOT open? Well, to Access it now just looks like a parameter query and the same Enter Parameter Value dialog box appears, prompting the user to enter the not-so-intuitive ID value, only this time, the prompt message is more cryptic than before.
Now maybe this will never happen in your applications, so go ahead and use references to form variables. It's not an evil technique, but it lacks the finesse of our next method ...
Queries built with brick
Fancy filtered queries, according to our requirements above, are smart. They're smart because we've made them smart. We, the developers, have an idea where to get the filter parameters and if they're not there, we presumably know what to do next. Maybe, if the form isn't open, we flash a warning message and shut down the user requested process. Perhaps we simply return the report with no data, or we could log the request as an error. The point is, when we offload the processing to VBA code, we can do anything we wish.
Below is a screen shot of this month's demo app, available in the download. The top part of the form shows the current author record information. The top two buttons demonstrate how to implement the methods described in the section above. The bottom portion of the form demonstrates two different ways to implement the Fancy Filtering technique.
The first implementation uses a pair of module level functions: SetAuthorID() and GetAuthorID(). The SetAuthorID() may be executed wherever it makes sense in your application, but for our demo, we will update the property each time the form navigates to a new record. In the Form_Current() event, the current au_id is passed to SetAuthorID() and the private module level variable, m_sAuthorID, is updated with the new value. Now that the private variable is updated, the GetAuthorID() function can retrieve it for use in the query.
Option Compare Database Option Explicit Private m_sAuthorID As String Public Function SetAuthorID(ByVal sID As String) As Boolean m_sAuthorID = sID End Function Public Function GetAuthorID() As String GetAuthorID = m_sAuthorID End Function
This is a simple, but effective way of saving the record ID while the program is running, but the value is saved in RAM memory and is of course lost when the user closes the app. In most cases, that is all that's required, but what if you wanted to 'remember' over time, the records visited by a user. A slightly modified version of the above functions will do the job.
Below is the code for the new functions, SetHistoryAuthorID() and GetHistoryAuthorID(). This code presupposes there is a history table named [UserRecordHistory] that is updated each time a user navigates to a new record. The code for SetHistoryAuthorID() creates the history record, assuming the table is 'authors' and the user is CurrentUser(). (Note that this built-in Access function will always return Admin unless Access security has been implemented.)
The code for GetHistoryAuthorID() is a little more involved. First, it looks in the UserRecordHistory table to find the latest record. But what if the current user has no history record? Rather than failing and returning nothing, this Smart Lookup takes another tack. It checks the private module variable to see if perhaps it is populated. If not, then it checks to see if frmDemo happens to be open. If not, then it finally gives up and returns nothing.
Public Function SetHistoryAuthorID(ByVal sID As String) As Boolean Dim sSQL As String sSQL = "INSERT INTO UserRecordHistory ([TableName], [RecordID], [UserName]) " & _ "VALUES ('authors','" & sID & "','" & CurrentUser() & "');" CurrentDb.Execute sSQL End Function Public Function GetHistoryAuthorID() As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim sSQL As String sSQL = "SELECT TOP 1 RecordID FROM UserRecordHistory " & _ "WHERE [TableName]='authors' " & _ "AND [UserName]='" & CurrentUser() & "' " & _ "ORDER BY [DateEntered] DESC" Set dbs = CurrentDb Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) If Not rst.BOF And Not rst.EOF Then GetHistoryAuthorID = rst!RecordID Else ' History is empty ... try something else If m_sAuthorID <> "" Then GetHistoryAuthorID = m_sAuthorID Else If CurrentProject.AllForms("frmDemo").IsLoaded Then GetHistoryAuthorID = Nz(Forms!frmDemo!au_id, "") Else GetHistoryAuthorID = "" End If End If End If Set rst = Nothing Set dbs = Nothing End Function
The last option is to open a custom dialog form that allows the user to select an author by name, but have their ID saved to the query. For this scenario, the demo form opens the author lookup form, but passes the name of the query in the OpenArgs property. Once the user selects an author, the ID value is saved using SetAuthorID() from the code above, the lookup form is closed and the query opened.
One more to go
That concludes Part III of the Queries On Steroids series. I've saved the hardest for last: Jet ShowPlan. Check back next month to see if I can pull this off. There's little written about this virtually hidden and seldom used feature of Microsoft Access. If you have questions or would like to suggest a query topic, email me at Danny Lesandrini with your comments.