The Find Methods

There are four Find methods: FindFirst, FindPrevious, FindNext, and FindLast. Their purpose is self-evident, given their names, and you can use them on all recordset types.

The Seek method always uses a table's indexes to execute the search, and although the Find methods will too (if they can), they can just as easily use a table scan to find the right record; it just depends on the type of search, and amount of data being searched. Not surprisingly then, the Find methods are usually far slower than using Seek.

Notwithstanding, the Find methods can be used on filtered dynaset and snapshot recordsets, which minimizes the number of records that have to be searched.

In addition, since you have FindNext and FindPrevious methods at your disposal, you don't have to start at the beginning or end of the recordset to find subsequent matches; you can just keep searching until you find the record you want.

All four methods have the same syntax:

rs.[FindFirst | FindPrevious | FindNext | FindLast] criteria

The criteria argument can be any valid SQL WHERE clause, without the word WHERE. For example, the following code demonstrates how to find the first and second instances of a customer having the word parts in his or her name.

'Search for the first matching record rst.FindFirst "[OrgName] LIKE '*parts*'"

'Check the result

If rst.NoMatch Then

MsgBox "Record not found."


MsgBox "Customer name: " & rs.CustName 'Search for the next matching record rst.FindNext "[OrgName] LIKE '*parts*'"

'Check the result If rst.NoMatch Then

MsgBox "Record not found."


MsgBox "Customer name: " & rs.CustName End If End If

Not a very object-oriented piece of code, but it serves to illustrate how to use these methods.

Once a matching record is found, any subsequent search begins from the current cursor position, not the start or end of the recordset, like in the Seek method. As with the Seek method, always follow the search with a check of the recordset's NoMatch property, to determine the result of the search.

0 0

Post a comment