Microsoft Access 2010 Bible

Part IV: Professional Database Development

888

Note
Criteria can be as complex as you need them to be, even involving multiple fields of different data types.
Remember that strings must be delimited by single quotes (not double quotes, because double quotes surround
the entire string), dates are delimited by pound signs (#), and numeric values are not delimited.

The FindFirst or Bookmark method is preferable to using FindRecord because it allows for

more complex criteria and doesn’t require the control being searched to be visible. You don’t have

to preposition the cursor on a control to use the recordset’s FindFirst method.

Note
In case you’re wondering, the recordset created from the form’s RecordsetClone property is a DAO-type
recordset. Only DAO recordsets support the FindFirst, FindLast, FindNext, and FindPrevious
methods. There is no reason for Microsoft to re-architect Access forms (and reports, for that matter) to use
ADO-type recordsets. The DAO model works very well when working with bound forms and reports.

Filtering a Form

Although using the FindRecord or FindFirst methods allow you to quickly locate a record

meeting the criteria you want, it still shows all the other records in a table or query recordset and

doesn’t necessarily keep all the records together. Filtering a form lets you view only the record or

set of records you want, hiding all non-matching records.

Filters are good when you have large recordsets and want to view only the subset of records

matching your needs.

You can filter a form with code or with a query. I cover both approaches in this section.

With code

Figure 25.6 shows the two lines of code necessary to create and apply a filter to a form’s recordset.

Each form contains a Filter property that specifies how the bound records are filtered. By default,

the Filter property is blank and the form shows all the records in the underlying recordset.

FIGURE 25.6

Code for filtering and clearing a filter behind a form