|| Home | Excel | Access | Word | Outlook | FrontPage | VBA | Downloads | Index ||
Or free-text filtering where the user can search on any text string:
In my combo box and list box examples, the fields that I have chosen for filtering contain a known range of entries. One is a list of company Offices and another is a list of Departments. My database has tables containing lists of these entries and I am using these tables for the Row Source of the combo boxes and list boxes. Later in this tutorial I will show how to use wildcards to allow users to filter records using string fragments (such as records starting with a particular letter, or containing a particular group of letters).
The important parts of the VBA code for each dialog box are described and explained below. To see the full code module for each dialog box follow the links marked "Code Listing 1" etc. These will display all the code used in the dialog box in a form that you can copy and paste into your own code window if you wish.
This is the simplest of the designs. The dialog has two combo boxes, one for each of the fields that I am allowing the user to filter. You can add as many combo boxes as you like providing you modify the code accordingly. There are also two command buttons: one for applying the filter and another for removing it.
The illustration below shows the layout of the dialog and the names I used for the various objects...
The aim of the code behind the Apply Filter button is to construct an SQL WHERE clause using the user's combo box choices. The SQL will then be applied to the report's Filter property and the FilterOn property of the report will be set to True so that it displays the filtered recordset.
If the user makes a choice from a combo box the report should display only the records which have that value in the field. So if, for example, they choose New York from the Office combo box the code will have to construct SQL like this:
[Office] = 'New York'
But if the user leaves a combo box empty, I want the report to display all the records for that field, which would require SQL like this:
[Office] Like '*'
Coding the "Apply Filter" Button
My code starts by declaring three string variables, one each to hold the SQL from the combo boxes and one to hold the combined criteria for the filter:
If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice and then construct the appropriate SQL:
There is a similar If Statement for each combo box:
Next comes a line which combines the criteria to form a WHERE clause for the filter:
strFilter = "[Office] " & strOffice & " AND [Department] "
And finally the filter is applied to the report and switched on:
Before completing any project like this you should consider what might go wrong. All but the most basic of your VBA procedures should contain a simple error handler, but you can help things a great deal by anticipating what problems might arise. Changes in the design of the report or the fields included might result in an error, but if you are fairly confident that this won't happen you can leave it for the error handler to take care of. This project requires the report to be open before the filter is applied. If it isn't then you get an error:
Notice that the error doesn't distinguish between the report not being open and not existing at all. It can only "see" an object if it is open. It may be that the user simply forgot to open the report, or perhaps its name has been changed or someone might have deleted it.
You could have the report open automatically when the dialog opens (or vice versa) by including the procedure:
Private Sub Form_Load()
But this would still need to take account of a missing or renamed report. My preferred method of dealing with this possibility is to check whether or not the report is open and advise the user accordingly. This code goes at the beginning of the cmdApplyFilter_Click procedure after the variable declarations:
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
If a report with the name specified is not found in an "Open" state the If Statement displays a message to the user then terminates the procedure to prevent an error occurring.
Coding the "Remove Filter" Button
All that is required to remove the filter is a line which sets the FilterOn property of the report to False:
Private Sub cmdRemoveFilter_Click()
The line On Error Resume Next tells Access to ignore any error which might occur if, for example, the report has already been closed by the user.
You can review the complete code for the dialog box in Code Listing 1.
Combo boxes are not the only way to offer the user a choice of predefined options. This dialog box, otherwise exactly the same as the previous example, makes use of an option group to allow the user to filter the records by gender.
Option groups are useful when there are just a few items to choose from. Here there are three choices: Female, Male or Both...
When you design your dialog box you can create an option group manually or with the help of the wizard. An Option Group consists of a Frame containing a collection of Option Buttons (sometimes called Radio Buttons). Alternatively you can use check boxes or toggle buttons.
If you use the wizard to create the option group all the work is done for you. If you prefer to create the option group manually, first draw a frame on the form then name it and add a suitable caption to the label. Then add the individual buttons inside the frame. As you do this each button is automatically assigned a value. The first button you add has the value 1, the next has the value 2 and so on. In my example the values are: Female (1), Male (2), Both (3). You can assign any value you like to each button by changing its Option Value property. You must use whole numbers (data type Long Integer) and normally they should all be different.
When option buttons are used as a group like this, only one member of the group can be selected at a time. The selected button passes its value to the group itself. It is a good idea to set a starting value for the group by specifying the frame's Default Value property. In my example the default value is 3, so that when the dialog box opens the Both button is already selected.
Detecting and programming the user's choice is simple and is best done with a Case Statement. First it is necessary to declare an additional variable to hold the Gender criteria:
Dim strGender As String
Then the Case Statement can place the appropriate criteria string into the variable depending on the value of the option group:
The only other change to the code is the addition of the criteria for the Gender field to the filter string:
strFilter = "[Office] " & strOffice & " AND
You can review the complete code for the dialog box in Code Listing 2.
The combo boxes used in the previous examples have the disadvantage that the user can select only one item from each. This dialog box uses list boxes. These offer a multi-select facility so that the user can make several choices from each if they wish.
Designing a list box is similar to designing a combo box in that is needs a Row Source from which to make its list. As before I have used tables for this. You must enable the multi-select feature by choosing a suitable mode for the Multi Select property of the list box. Choose Simple or Extended depending on how you want the user to make their choices.
If you want to know more about list box basics read my tutorial Making Sense of List Boxes.
In this example the code has the job of creating a criteria string from the user's choices in each of the list boxes. If, for example, the user chooses London, Brussels and Paris from the Office list box it means that they want to see records for the London OR Brussels OR Paris offices.
There are different ways this can be represented in SQL. One way is to use the keyword "OR" like this:
[Office] = 'London' OR [Office] = 'Brussels' OR [Office] = 'Paris'
but this can result in very long criteria strings, so I prefer to use an SQL "IN" clause like this:
To do this I use a code statement that loops through the selected items in the list box and strings them together, separated by commas:
Dim varItem As Variant
If the resulting string has no length (i.e. it is empty because the user didn't select anything) the code creates criteria which shows all records. But if the user did make a selection my code creates an IN clause listing the selections, after first removing the leading comma from the front of the string:
If Len(strOffice) = 0 Then
Each list box requires a similar set of code statements. The code for the Gender option group and the code that creates the filter string are exactly the same as in the previous example.
You can review the complete code for the dialog box in Code Listing 3.
In many cases sorting the records in a report is just as important as filtering them. If you don't want to offer a sorting option to the user, you can predefine the sort order by basing the report on a query or use an SQL statement incorporating an ORDER BY clause as the report's Record Source. For example:
SELECT * FROM tblStaff ORDER BY [Office], [Department];
will display the records sorted first by the Office field then by the Department field, both in ascending order.
Adding sorting options to the dialog increases its power a great deal but to make it a really useful and user-friendly tool requires a bit of thought and quite a lot of code (although much of it is quite simple and repeated several times). Here's how the dialog box looks:
I have put the sorting tools (three combo boxes and three command buttons) inside a frame purely for design effect. It has no other practical implications. The features of the sorting tools are as follows:
As you can see, there is quite a lot of functionality here and that requires quite a lot of code, but it is fairly simple and much of it is repeated for each combo box so the task of creating an ORDER BY clause isn't as daunting as it might seem.
Filling The Combo Box Lists
To create the list, each combo box has its Row Source Type property set to Value List. This allows a list of items to be typed directly into the Row Source property textbox:
Separated by semicolons, each item becomes an option on the combo box list:
Coding the Combo Boxes
Each combo box has three jobs to do when the user makes a choice:
The first job is best handled by the combo box's Before Update event. This is because, if the user's choice is not acceptable, the update can be cancelled easily. The After Update event doesn't offer this facility. Here's the code for the first combo box:
Private Sub cboSortOrder1_BeforeUpdate(Cancel As Integer)
There are two If Statements, one inside the other (this is referred to as "nested"). The first If Statement checks to see if the user chose "Not Sorted". If they did, nothing happens because it's OK if more than one combo box has this value. But if any other value has been chosen it checks to see if the chosen value matches any of the other combo boxes. If it does a message is shown to the user, their action is undone (by cancelling the event) and the combo box list is dropped to prompt the user to make a different choice.
The Before Update event procedure of each combo box has similar code with the numbers changed so that each combo box checks the other two.
The remaining jobs are handled by the combo boxes' Change event:
Private Sub cboSortOrder1_Change()
When the user changes the value of the combo box an If Statement checks the new value to see if it is "Not Sorted". If it is, a For...Next loop disables the other combo boxes and sets their values to "Not Sorted". Then another For...Next loop disables the command buttons and sets their captions to "Ascending".
If the user made a choice other than "Not Sorted" then its own sort direction command button is enabled and the combo box below is enabled.
As before, the code is similar for the remaining combo boxes. Each one referring to the appropriate controls.
NOTE: One of the aims of writing good code is to make it as brief as possible, and in the above example I have demonstrated this with the use of loops and With Statements. Using loops avoids having to repeat chunks of code. The variable "i" not only tells Access how many times to run the loop but also serves to identify which control is being manipulated each time. I can do this because I gave the controls names which included numbers. In the first loop, the first time it runs the value of "i" is 2 so Me.Controls("cboSortOrder” & i) means the same as Me.cboSortOrder2 and so on. Although there are only three combo boxes in this example the loop would require no additional code for any number of combo boxes. I would just have to change the upper limit of the value of "i". With Statements also help by removing the need to repeat the first part of a long code line when writing several consecutive statements all referring to the same thing.
Coding the Sort Direction Command Buttons
I want to make the selection of a sort direction (i.e. ascending or descending) as easy as possible. Since there are only two choices it isn't worth using combo boxes, and an option buttons would take up too much room. So I borrowed Microsoft's idea (take a look at the Query Wizard!) and used command buttons. Clicking one of the command buttons doesn't do anything other than change it's caption. The code which later builds the SQL statement for the report filter will read the button's caption to determine which way to sort the records. The code is simple and the same for each button (with the appropriate control names inserted):
Private Sub cmdSortDirection1_Click()
Coding the Apply Filter Command Button
The code used to create the filter string is exactly the same as in the previous example, but this time there is an additional task: to create an Order By string that will be applied to the OrderBy property of the report.
In the same way that the code generates a criteria string, applies it to the Filter property of the report and then activates it by setting the FilterOn property to True, it now has to generate a sort string, apply it to the OrderBy property of the report and activate it by setting the OrderByOn property to True.
If the user chose to sort the data by LastName then by FirstName, here's what a completed OrderBy string should look like:
Fields are simply listed in the desired order, separated by commas. I am in the habit of enclosing field names in square brackets as shown here but this is only absolutely necessary when the names contain spaces.
Unless specified otherwise, each field is sorted in ascending order (A-Z, 1-9) but if a field is to be sorted in descending order (Z-A, 9-1) the keyword DESC is used. The sort direction must be specified for each field. if it is omitted it is assumed that the field is to be sorted in ascending order. So, if the user chose to sort the data by LastName then by FirstName both in descending order the string should look like:
[LastName] DESC, [FirstName] DESC
Here is the code used to build the sort string:
If Me.cboSortOrder1.Value <> "Not Sorted" Then
The code consists of a series of nested If Statements, each one depending on the result of the previous one. First of all, an If Statement checks to see if the value of the first combo box is something other than "Not Sorted". If the value is "Not Sorted" the If Statement finishes and the OrderBy string remains empty. But if the first combo box contains a field name then it is placed, surrounded by square brackets, into a variable named strSortOrder. A second If Statement then looks at the caption of the first command button and if it reads "Descending" the keyword DESC is added to the string in the strSortOrder variable.
The process is repeated for each combo box and finishes if the value "Not Sorted" is found, but if a field name is found its name and the sort direction is noted and the code moves to the next combo box.
The last section of the code applies and activates both the filter string and the sort string:
Coding the Remove Filter Command Button
In the earlier examples the this button has simply set the FilterOn property of the report to False. Now it has to do the same for the OrderByOn property:
But this time I have added some extra functionality. The following statement removes the selections from the Office list box (there is a similar one for the Department list box):
For Each varItem In Me.lstOffice.ItemsSelected
The option group is reset to its original value (Gender = Both):
Me.fraGender.Value = 3
Finally the following loop sets the value of each combo box to "Not Sorted" and disables it, then sets the caption of each sort direction command button to "Ascending" and disables it. The last line enables the first combo box ready for the user to make their next choice.
For i = 1 To 3
You can review the complete code for the dialog box in Code Listing 4.
Each example so far has offered the user a fixed range of choices for filtering the report's records. That is fine if the fields that are being filtered contain a known range of items. But you might need to offer the facility to filter using free text, i.e. allowing the user to enter anything they want.
This final example shows how you can allow the user to enter any string of text into a text box and choose how that string is used to filter the records...
For each available field the dialog box has a text box and a set of option buttons. The user can enter a letter or a string of text in the text box and make a choice from the option group to specify how the string is used in the filter. If the user leaves a text box empty then all records are returned for that field.
Supposing the user might enter the string mar into the FirstName textbox. The results they get will depend upon the choices they make from the option buttons:
with... would return: Martin, Mark, Margaret,
The method used by the filter is to combine the string with one or more asterisk wildcards. In SQL the asterisk (*) combined with a string and the keyword LIKE represents any string of text:
represents the letter g followed by any string
The code used to create the filter string is quite simple:
If IsNull(Me.txtFirstName.Value) Then
An If Statement looks for an entry in the text box. If the text box is empty (i.e. its value is Null) a filter expression is created that will return all the records. If there is an entry in the text box a Case Statement creates the required filter expression depending on the value of the option group.
This is repeated for each field and the filter expressions are combined to create a filter string:
strFilter = "[FirstName] " & strFirstName & _
The filter string is applied to the report and activated in the same way as in the previous examples.
You can review the complete code for the dialog box in Code Listing 5.
Ideas for Further Improvements
The examples I have shown are each designed to illustrate particular techniques. Remember you can mix and match as many of the different tools as you need but remember to always think about the user. These tools are meant to make their lives easier - so keep it simple!
Opening the Report Automatically
The above examples include a few lines of code that look for the open report and warn the user if it isn't found:
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
But you could save them the trouble by opening the report automatically as soon as they press the Apply Filter button:
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
Or perhaps you could ask the user what they want to do:
Dim Response As VbMsgBoxResult 'For Access 97 use: As Variant
Automate the Report's Title
Most reports need some sort of descriptive title and you might like the title to change to reflect the records displayed. You can use the user's choices in the combo boxes, list boxes and other controls to construct a variety of labels to place in the header or footer section of the report.
For each title you need to place an empty control in the report's Report Header or Report Footer section. If these sections are not already shown on your report when you view it in Design View, go to View > Report Header/Footer to switch them on.
You can either add a Label control (and use the code to specify its Caption property) or an unbound TextBox control (and use the code to specify its Value). My preference is to use a TextBox because it has a Can Grow property which, when set to Yes, allows the control to increase in height to accommodate the amount of text it contains. Also, its value can be set when the report is in Preview view (unlike a label caption which requires the report to be switched into Design view for editing). This is useful if you aren't sure how long the title is going to be. Make sure you draw the control as wide as the report permits because the width doesn't change, only the height:
The text of your title can be made up of plain text combined with values extracted from the dialog box controls. It can be applied to the report at the same time as the filter. Here's an example of the sort of code you might use (the textbox control is named txtReportTitle):
Note the use of vbCrLf in the code to add line breaks to the finished text. Here's the result:
You can download a fully-working database file containing this tutorial's completed project in either Access 97 or Access 2000 format. The database contains all five dialog boxes described in the tutorial and a sample report and tables. The files are provided in Access 97 and Access 2000 format.
©2004 Martin Green. All rights reserved. firstname.lastname@example.org