You can use the Recordset's Find method to move the cursor to a record that fits
a specified criterion.
The Find method takes up to four arguments:
-
Criterion (required) - A string with the same syntactic format
as a SQL where clause. It specifies a condition that the record being sought must
fulfill.
-
SkipRows - A number representing the offset of the starting
position of the search from either the current row or from the row indicated by
the Start argument. Assumed to be 0 if left blank.
-
searchDirection - A flag indicating the direction in which
to search from the starting point. Values can be adSearchForward or adSearchBackward.
Assumed to be adSearchForward if left blank.
-
Start - A Double-type value that gives the Bookmark of the
record from which the search will begin in the direction indicated by searchDirection.
Assumed to be the current row if left blank.
When you call the Find method, of course, it is not always certain that you
will find a record that fits your criteria. If the Find method does not locate
any records, the cursor ends up at the very beginning or end of the Recordset
(depending on the setting of the searchDirection argument), and the BOF or EOF
property will be True.
You should always check the BOF and EOF properties after you perform a Find
so that you can gracefully recover from an unsuccessful attempt to find records.
Listing 8.12 gives an example of the use of the Find method. In this implementation,
the user enters a string or partial string in a TextBox. The code incorporates
the string into a condition and uses this condition as the Criterion argument
to the Find method.
LISTING 8.12
USING THE FIND METHOD TO LOCATE RECORDS IN A RECORDSET
Private Sub cmdFind_Click()
'Note: Bookmark only works correctly
'with Client-side cursors
Dim dBookmark As Double
dBookmark = rsEmployees.Bookmark
Dim sFindCriterion As String
sFindCriterion = "LastName like '" & _
txtLastNameToFind & "*'"
rsEmployees.MoveFirst
rsEmployees.Find sFindCriterion, , adSearchForward
If rsEmployees.EOF Then
rsEmployees.Bookmark = dBookmark
MsgBox "Couldn't Find """ & txtLastNameToFind & """*"
Else
txtLastNameToFind = ""
End If
End Sub
