Microsoft Access 2010 Bible

Chapter 25: Advanced Data Access with VBA

889

The first line of code sets form’s Filter property:

Me.Filter = “ProductID = “ & cboQuickSearch.Value

Notice that this is exactly the same string used as the criteria passed to the recordset’s FindFirst

property.

The second line of code (Me.FilterOn = True) turns on the filter. You can put all the criteria

that you want in a filter property, but unless you explicitly set the FilterOn property to True,

the filter is never applied to the form’s recordset. The filter hides all the records that do not meet

the criteria, showing only the records meeting the filter’s value.

Me.FilterOn = True

Whenever you turn on a filter, it’s useful to provide a way to turn the filter off. If you look at the

top of Figure 25.6, you can see a small button (cmdClearFilter) next to the combo box. This

button turns off the filter and sets the form’s Filter property to an empty string (vbNullString).

The second procedure shown in Figure 25.6 is the button’s Click event procedure:

Private Sub cmdClearFilter_Click()

Me.Filter = vbNullString

Me.FilterOn = False

End Sub

Caution
If you create a form filter and then save the form design with the filter set, the filter is saved with the form. The
next time the form is opened, the filter is active. It’s a good practice to set the form’s Filter property to an
empty string as the form closes. The following code uses the form’s Close event procedure to clear the filer:

Private Sub Form_Close()

Me.FilterOn = False

Me.Filter = vbNullString

End Sub

With a query

You might want to have one form control another. Or you might want a recordset to display

selected data based on ad hoc criteria entered by the user. For example, each time a report is run, a

dialog box is displayed and the user enters a set of dates or selects a product or customer. One way

to do this is to use a parameter query.

Creating a parameter query

A parameter query is any query that contains criteria based on a reference to a variable, a function,

or a control on a form. Normally, you enter a value such as SMITH, 26 , or 6/15/12 in a criteria

entry area. You can also enter a prompt such as [Enter the Last Name] or a reference to a control

on a form such as Forms!frmProducts![cboQuickFind].