Microsoft Access 2010 Bible

Chapter 25: Advanced Data Access with VBA

887

Tip
Creating criteria in code is sometimes a complicated process. The objective is to build a string that could be
copied into a query SQL window and run as is. Often, the best way to create a criteria string is to build a
query, switch to SQL view, and copy the SQL into a VBA code window. Then, break the code’s WHERE clause
into field names and control values, inserting concatenation operators and delimiters as needed around string
and date values

After the criteria string is completed, you use the recordset’s FindFirst method to search for the

record in the RecordsetClone. The following line uses the FindFirst method of the record-

set, passing the criteria string as the argument:

rs.FindFirst strCriteria

Note
You don’t have to create a Criteria variable and then set the criteria string to it. You can simply place the
criteria after the rs.FindFirst method, like this:

rs.FindFirst “ProductID = “ & cboQuickSearch.Value

However, when you have complex criteria, it may be easier to create the criteria separately from the command
that uses the criteria string so you can debug the string separately in the query editor.

The next lines are used to determine whether the record pointer in the form should be moved.

Notice the Bookmark property referenced in the following code block. A bookmark is a stationary

pointer to a record in a recordset. The FindFirst method positions the recordset’s bookmark on

the found record.

If Not rs.NoMatch = True Then

Me.Bookmark = rs.Bookmark

End If

If no record was found, the recordset’s NoMatch property is True. Because you want to set the

bookmark if a record is found, you need the computer equivalent of a double negative. Essentially,

it says if there is “not no record found,” and then the bookmark is valid. Why Microsoft chose

NoMatch instead of Match (which would reduce the logic to If rs.Match Then...) is a mys-

tery to everyone.

Here’s an alternative way to write the logic for checking the NoMatch property:

If rs.NoMatch = False Then

Me.Bookmark = rs.Bookmark

End If

If a matching record is found, the form’s bookmark (Me.Bookmark) is set to the found recordset’s

bookmark (rs.Bookmark) and the form repositions itself to the bookmarked record. This does

not filter the records — it merely positions the form’s bookmark on the first record matching the

criteria. All other records are still visible in the form.

The last lines of code simply close and discard the recordset.