The Find Method

Unlike DAO, ADO only has one Find method. The Find method has the following syntax: rs.Find Criteria, SkipRows, SearchDirection, Start

The Criteria argument can be any valid SQL WHERE clause, without the word WHERE. The SkipRows argument is the number of rows to skip when searching for the next or previous match. The Start argument is a bookmark that you can use as a starting point for the search. And lastly, the SearchDirection argument can be either adSearchForward or adSearchBackward, the function of which is fairly obvious.

Unless otherwise specified, all searches begin at the current row, so it's a good idea to always issue the MoveFirst method before attempting Find when you first open a recordset.

The following code demonstrates how to find the first and second instances of a customer having the word parts in their name.

'Search for the first matching record rs.Find "[OrgName] LIKE '*parts*'", , adSearchForward rs.MoveFirst

'Check the result If rs.NoMatch Then

MsgBox "Record not found."

Else

MsgBox "Customer name:" & rs.CustName 'Search for the next matching record rs.Find "[OrgName] LIKE '*parts*'", 1, adSearchForward

'Check the result If rs.NoMatch Then

MsgBox "Record not found."

Else

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

Notice that the SkipRows argument is specified in both searches. This is because you have to skip the current row too. Unfortunately, you can only specify a single column name in the search criterion. The Find method does not support multicolumn search.

Two interesting points to note are that literal string values can be specified either within single quotes or within hash characters. For example:

Also, the use of the asterisk as a wildcard character is restricted. You can specify it at the end of the criterion string, or at the beginning AND end. You cannot use the asterisk at the beginning (without one also being at the end), or in the middle. The following truth table illustrates this point.

State

LIKE

'*York'

Illegal

State

LIKE

'New*'

OK

State

LIKE

*ew Yor*'

OK

State

LIKE

'New *ork'

Illegal

Once a matching record is found, any subsequent search begins from the current cursor position, not from the start or end of the recordset, like 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