Filtering Data with Access Forms

Two approaches to programming custom filters

Access's popular filtering capability lets database users select a subset of items for viewing. A classic filter was the old push-button telephone directory, where you pressed the m button to contact people whose last names began with M. You can easily reproduce this example, and modern user interface and database programming techniques can improve the process. For example, database developers using Access forms to reference a SQL Server database can list only the first letters of last names in their contact database, instead of all letters.

In this article, I discuss two contrasting approaches to programming custom filters for the Pubs database with Access forms—filtering with the Filter property and filtering with the ServerFilter property. Both approaches rely on the authors table and let users filter authors by their home state. Your needs and experience will help determine the best method for you. Access lets you filter a recordset by using one method or the other in any situation, but you can't use both at the same time.

Creating a Filtering Form for the Authors Table

Access 2000 makes creating a form based on a SQL Server table easy, and adding filtering is uncomplicated. To start creating a form, open an Access project connected to the Pubs database by starting Access and choosing Project (Existing Database) from the New dialog box. After you assign a name to the new project, complete the Data Link Properties dialog box to point to the Pubs database. When the Access project opens, click the Tables icon in the Database window Objects bar, and highlight the authors table. Next, automatically generate a bound form to the authors table by clicking the AutoForm Wizard button on the toolbar above the Database window (it looks like a form with a lightning bolt to one side). Running this wizard creates a form for the table or view you selected.

Hold the cursor over any toolbar button to view a pop-up box that describes the button's purpose. (Sometimes you'll need to click a drop-down arrow to open a selection of controls associated with a single tool on a toolbar.) Study the functions of the toolbar controls until you can recall them on sight.

Next, add form controls to manage filtering. My samples rely on a combo box to let users pick a criterion for filtering. A command button clears filtering, so users can browse all records in the data cache for a form. By clicking the View button on the Form View toolbar, you can switch from Form view to Design view to position the controls on the form.

Add the combo box and command button to the form by dragging them from the Toolbox to the right side of the form. The Toolbox is a collection of control classes that lets you add controls to a form. You can open the Toolbox from the Form Design toolbar.

Figure 1, page 28, shows an evolving form that the AutoForm Wizard created for the authors table. Make room for the controls by dragging the form's right edge. Next, click the combo box and command button controls and drag them from the Toolbox to the form. To modify a form control's properties, select the control and click the Properties tool on the Form Design toolbar. This action opens a tabbed dialog box that lets you change the control's properties.

Figure 1 shows the command button after its name and caption have been set to cmdShowAll and Show All, respectively. Note that the combo box's label caption has changed from its default value to Filter authors on this state:. The combo box control's name is cboFilterState. To enable the combo box to list only states where authors live, type the following T-SQL statement in the combo box's Row Source property:

SELECT DISTINCT state FROM authors

After revising the form control's properties, you're ready to add event procedures that control filtering. Both filtering approaches use the same basic form, so save the form currently open in the Design view as Form1 to preserve your property assignments.

To add event procedures, open Form1 in Design view. Then, select the object for which you want to create an event procedure. If your event procedure applies to a control, such as a combo box, click that control to select it. If you want to apply an event procedure to a form, click within the form window, but outside of the form. After selecting an item for an event procedure, click the Properties control on the toolbar. Next, select the Event tab on the selected item's properties dialog box. Finally, click the Build button next to the designated event and choose Code Builder in the pop-up dialog box. This will launch the creation of the event procedure.

Figure 2 shows the Build button for the AfterUpdate event for the cboFilterState combo box. The Build button (and the Code Builder) sets up the shell for the event procedure, including the sub and end sub lines. Enter your custom Visual Basic for Applications (VBA) code between these lines. (You can view sample event procedure syntax in the listings.) Save the form.

Filtering with the Filter Property

Use a form's Filter property to specify a string that makes the form return a subset of the records its Record Source property designates. A form's Record Source property can be a table, view, stored procedure, or T-SQL SELECT statement. The Filter property also applies to reports.

When you use the Filter property with a form in SQL Server databases, first set the form's FilterOn property to False and save the form. You can insert the code for this task in the form's Open event procedure. Note that this step is unnecessary if you're using Access database files.

The code that assigns a value to a form's Filter property has a format similar to the WHERE clause of a SELECT statement (except without the WHERE keyword). In its simplest format, the expression for a Filter assignment is fieldname = value. After a user selects a state from the cboFilterState combo box, the control displays the user's selected state for filtering authors. Your application can set the Filter property for the form with an assignment expression in the AfterUpdate event procedure for the combo box.

After you set the property, turn on the filter by setting the form's FilterOn property to True. (Note that the FilterOn property is not available for a form when you design it.) Set the form's Filter property to a zero-length string (i.e., " ") when users signal they want to show all records again. (In this case, you don't need to set the FilterOn property to True.) Assign the zero-length string in the Click event procedure for the cmdShowAll command button.

The three event procedures in Listing 1 show the syntax for statements that manage the Filter property in the sample application. The Form_Open event procedure ensures that the FilterOn property is False when the form initially loads. The cboFilterState_AfterUpdate event procedure creates a string expression that sets state equal to the value in the cboFilterState combo box and assigns the expression to the current form's Filter property. The cmdShowAll_Click event procedure demonstrates the syntax for clearing any existing filters for a form.

Filtering with the ServerFilter Property

Access 2000 includes the new ServerFilter property. Like the previous Filter property, ServerFilter returns a subset of the records associated with a form's Record Source property. Furthermore, you use the same expression format to specify a ServerFilter property or a Filter property. But the method you use to invoke the ServerFilter property is different from the method you use to invoke the Filter property. Additionally, you don't need a Form_Open event procedure that ensures the ServerFilter property is off. Both Filter and ServerFilter are available for forms and reports, but data access pages—the data-based Web pages,c which Office 2000 introduced—can use only ServerFilter.

To implement filtering with ServerFilter, the code in the sample requires only two event procedures, as Listing 2 shows. The cboFilterState_AfterUpdate event procedure requires an expression to assign a string value to the form's ServerFilter property. (This expression is the same one you used to set the Filter property in the preceding example.) Instead of invoking the filter by assigning a value of True to the FilterOn property, you invoke a ServerFilter property by applying the Refresh method to the form. (Note that Access 2000 Help documentation incorrectly instructs you to apply a ServerFilter assignment by assigning True to the form's ServerFilterByForm property. In fact, assigning True has no effect on filtering of the form's records.) The cmdShowAll _Click event procedure reverses the application of the ServerFilter expression by assigning a zero-length string to the property and invoking the form's Refresh method.

I've demonstrated two approaches to filtering SQL Server databases on Access 2000 forms. Dynamic filtering is a powerful feature that helps users take charge of data on their forms. Developers who use the traditional Filter property with Access database files will feel comfortable with this technique as they migrate to SQL Server databases.

The ServerFilter property works more efficiently with SQL Server and Access forms (for example, you don't have to turn off the Server Filter property before you can turn it on). In addition, ServerFilter works for data access pages as well as Access forms and reports.