Finding Data in a Recordset

Often, you'll need to pinpoint a specific record rather than just move from one record to another. ADO's Find method is flexible and efficient, but it doesn't enable you to combine multiple conditions using the And operator. The Find method uses the syntax

 recordset.Find criteria[, skiprows][, searchdirection][, start] 

Only the first of these arguments is required:

• criteria A String value in the form of a SQL WHERE clause, but without the WHERE keyword.

• skiprows A Long value that specifies the number of rows from the current row where the search will begin. If omitted, Find starts with the current row.

• searchdirection One of two intrinsic constants: adSearchForward (the default) or adSearchBackward.

• start A Variant value that specifies an optional bookmark that indicates where the search will begin. If omitted, Find starts with the current row. You can use a bookmark or one of the following intrinsic constants: adBookmarkCurrent, adBookmarkFirst, or adBookmarkLast.

TIP

When a search relies on more than one criterion, use the ADO Filter property in the form

 recordset.Filter = "condition1 AND condition2" 

Chapter 16 covers the Filter property in "Filtering Recordsets," p. 247.

The Find method enables you to find a record based on a search string. You supply that search string in the form of a SQL WHERE clause, without the WHERE keyword as follows:

 recordset.Find "field = value" 

The following procedure illustrates the Find method:

 Sub FindClients(str As String)   'accept string value as search criteria   'for ADO Find method   Dim rst As ADODB.Recordset   Dim strSearch As String   strSearch = "City = '" & str & "'"   Set rst = New ADODB.Recordset   With rst     .Open "Clients", CurrentProject.Connection, adOpenStatic, _      adLockPessimistic     .MoveFirst     .Find strSearch     Do Until .EOF       Debug.Print rst("Client")      .Find strSearch, 1     Loop   End With   rst.Close   Set rst = Nothing End Sub 

You can enter this procedure in a standard module or use Chapter 17's example module. Then, run the statement

 FindClients("North Hollywood") 

in the Immediate window. Figure 17.2 shows the results a list of all the clients listed in North Hollywood.

Figure 17.2. Locating data with the Find method.

After creating and populating the Recordset object, the Find method locates the first record where the value of the City field equals the value passed by the variable str. In this example, that's "North Hollywood." The Do loop prints the Client value for that record and then executes a second Find using the same criterion. Notice that the Find method's skiprows argument negates the need to add a MoveNext method. If you omit that value, you must precede the Find method with a MoveNext method. Otherwise, the loop hits a terminal snag and prints the Client value for the current record over and over.

You can also use operators other than = with the Find method. For instance, you can use City > 'Detroit' to find the first city after Detroit in the data.

An Alternative to Find the ADO Seek Method

When circumstances warrant, the ADO Seek method is actually a better choice than Find because it's faster. However, two important conditions must be met before the Recordset can support the Seek method:

• The Recordset type must be a direct table, opened using the adCmdTableDirect option. This option is specific to Microsoft Access databases (remember, ADO itself also works with other types of data).

• You must set the Recordset's Index property to an index in the underlying table that includes the search field.

NOTE

To use Seek, you must know the name of the index on the search field in the underlying table. Most of the time, that's no problem because you name the index when you create it, whether you create the index manually or programmatically. If you fail to name the index when creating it programmatically, Access combines the names of the fields to create a name. Primary key fields are a little different. Access names the primary key index PrimaryKey. (You can override that if you create the primary key programmatically.)

You can also use code to discern index names if you don't know the names. To do this, you need to use ADOX, which you learn more about in Chapter 18. As a little preview, you can use the following procedure to return a list of indexes in the Immediate window:

 Sub FindIndex(tblName As String)   ' Find all indexes for a table   Dim cat As ADOX.Catalog   Dim tbl As ADOX.Table   Dim idx As ADOX.Index   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   Set tbl = cat.Tables(tblName)   For Each idx In tbl.Indexes     Debug.Print idx.Name   Next idx End Sub 

CAUTION

When using Seek, keep in mind that you can't index a Memo, Hyperlink, or an OLE Object field.

The Seek method uses the following syntax:

 recordset.Seek indexname, option 

where option is one of the intrinsic constants listed in Table 17.2. Before the constants make sense, you need to understand how Seek works. Seek doesn't search the entire field for a literal value in what's known as a table scan. That's how Access finds data in a field that's not indexed.

Table 17.2. Seek Option Intrinsic Constants

Constant	Explanation
adSeekAfterEQ	Select the index key value that's equal to the search value. If there's no matching value, stop at the key immediately following the key where the match would have occurred (which is only possible because the internal values are sorted).
adSeekAfter	Select the first index key value after the point where a match occurs or would have occurred.
adSeekBefore	Select the first index key value before the point where a match occurs or would have occurred.
adSeekBeforeEQ	Select the index key value that's equal to the search value. If there's no matching value, stop at the key immediately before the key where the match would have occurred.
adSeekFirstEQ	Select the first index key value that's equal to the search value.
adSeekLastEQ	Select the last index key value that's equal to the search value.

The internal workings of an index are complex, but for the purposes here, think of an index as a sorted lookup table that stores key values and pointers to each entry in the indexed field.

The index is really just a list of values with a pointer to the actual row where that data is stored. Because the values are sorted (internally), Access can quickly tell where the value ought to be. Seek looks for key values that are equal to the value for which you're searching without scanning the entire table.

The following procedure uses the Seek method to find a specific client in TimeTrack's Clients table:

 Sub SeekClient(Clientid As Long)   'Find client record using   'ADO Seek method   Dim rst As New ADODB.Recordset   With rst     .Open "Clients", CurrentProject.Connection, _      adOpenDynamic, adLockOptimistic, adCmdTableDirect      .Index = "PrimaryKey"     .Seek Clientid, adSeekAfterEQ   End With     If rst.EOF Then       MsgBox "There are no records for client " _        & Clientid, vbOKOnly     Else       MsgBox "Client " & Clientid & " is " & rst("Client") _        , vbOKOnly     End If     rst.Close     Set rst = Nothing End Sub 

As usual, you can enter the procedure in a standard module or use Chapter 17's example module. In the Immediate window, run the following statement:

 SeekClient(1) 

After creating and populating the direct table Recordset object, the Index property is set to the primary key index. Then, the Seek method is used to find the first record with the value 1 in the ClientID field (the primary key field). The If statement displays the corresponding client's name, as shown in Figure 17.3. If there's no match for the value passed by clientid, the procedure displays an appropriate message. Either way, click OK to clear the message.

Figure 17.3. You can display the data found by the Seek method.