Finding Records in a Recordset Object

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Both DAO and ADO have two mechanisms for locating a record in a Recordset object: the Find and Seek methods. With both methods you specify criteria to be used to locate a matching record. In general, for equivalent types of searches, the Seek method provides better performance than the Find method, because the Seek method uses an underlying index to locate the record. For this reason, you can only use the Seek method on Recordset objects that are based on a table (opened by using the Options argument set to adCmdTableDirect) that has an associated index.

Using the Find Method

DAO includes four "Find" methods: FindFirst, FindLast, FindNext, and FindPrevious. The method you use is determined by where you want to start searching (from the beginning, end, or current record) and the direction you want to search (forward or backward). However, ADO has a single Find method. When you use ADO's Find method, the search always starts from the current record. The Find method has a SearchDirectionargument that allows you to specify the search direction, and a SkipRows argument that specifies an offset from the current record from which to begin searching. The following table shows how to map the four DAO Find methods to the equivalent functionality in the ADO Find method.

DAO and ADO require a different syntax for locating records based on a Null value. In DAO if you want to find a record that has a Null value you use this syntax:

"Region Is Null"

And if you want to find a record that does not have a Null value for that field, you use this syntax:

"Region Is Not Null"

ADO, however, does not recognize the VBA Is operator. You must use the = or <> operators instead. So the equivalent ADO criteria would be:

"Region = Null"

-or-

"Region <> Null"

So far, each of the criteria shown in the examples has been based on a value of a single field. However, with DAO Find methods, the Criteria argument is like the WHERE clause in an SQL statement and can contain multiple fields and comparison operators within the criteria. This is not the case with the ADO Find method. The ADO Find method's Criteria argument is a string that contains a single field name, comparison operator, and value to use in the search. If you need to find a record based on multiple fields, use the Filter property (see "Filtering and Sorting Data in a Recordset Object" later in this chapter) to create a view of the Recordset object that contains only those records that match the criteria.

DAO and ADO also behave differently if no record that meets the specified criteria is found. The DAO Find and Seek methods set the NoMatch property to True and the current record is not defined. If the ADO Find and Seek methods don't find a record that meets the criteria, the current record is positioned after the end of the Recordset objectif you are searching forward (EOF = True), or before the beginning of the Recordset objectif you are searching backward (BOF = True). Use the BOF or EOF property as appropriate to determine whether or not a match was found.

The following example demonstrates how to locate a record by using the ADO Find method.

Sub FindRecord(strDBPath As String, _
               strTable As String, _
               strCriteria As String, _
               strDisplayField As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   
   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With
   
   Set rst = New ADODB.Recordset
   With rst
      ' Open the table by using a scrolling Recordset object.
      .Open Source:= strTable, _
            ActiveConnection:= cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

      ' Find the first record that meets the criteria.
      .Find Criteria:=strCriteria, SearchDirection:=adSearchForward

      ' Make sure record was found (not at end of file).
      If Not .EOF Then
         ' Print the first record and all remaining
         ' records that meet the criteria.
         Do While Not .EOF
            Debug.Print .Fields(strDisplayField).Value
            ' Skip the current record and find next match.
            .Find Criteria:=strCriteria, SkipRecords:=1
         Loop
      Else
         MsgBox "Record not found"
      End If
      ' Close the Recordset object.
      .Close
   End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

For example, to use this procedure to find records in the Customers table in the Northwind database that have "USA" in the Country field, you can use a line of code like this:

FindRecord "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _    "Customers","Country='USA'","CustomerID"

The FindRecord procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

Using the Seek Method

Because the ADO Seek method uses an index, it is a good idea to specify an index before you search. However, if you don't specify an index, the Jet database engine will use the primary key index.

If you need to specify a value for more than one field, use the VBA Array function to pass those values to the KeyValues argument of the Seek method. If you only need to specify one value, it is not necessary to use the Array function.

As with the Find method, you use the BOF or EOF property (depending on the search direction) to determine whether or not a matching record was found.

The following example shows how to locate a record by using the ADO Seek method.

Sub SeekRecord(strDBPath As String, _
               strIndex As String, _
               strTable As String, _
               varKeyValues As Variant, _
               strDisplayField As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   
   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   Set rst = New ADODB.Recordset
   With rst
      ' Select the index used to order the data in the Recordset object.
      .Index = strIndex
   
   ' Open the table by using a scrolling Recordset object.
      .Open Source:= strTable, _
            ActiveConnection:= cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic, _
            Options:=adCmdTableDirect
   
   ' Find the order where OrderId = 10255 and ProductId = 16.
      .Seek KeyValues:=varKeyValues, SeekOption:=adSeekFirstEQ
   
   ' If a match is found, print the value of the specified field.
      If Not .EOF Then
         Debug.Print .Fields(strDisplayField).Value
      End If

   ' Close the Recordset object.
      .Close
   End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

For example, to use the PrimaryKey index to find records in the Order Details table in the Northwind database where the OrderID field is 10255 and ProductID is 16 and display the value in the Quantity field, you can use a line of code like this:

SeekRecord "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "PrimaryKey", "Order Details", Array(10255,16), "Quantity"

The SeekRecord procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.