How to: Find a Record in a Dynaset-Type or Snapshot-Type DAO Recordset

Access Developer Reference

You can use the Find methods to locate a record in a dynaset-type or snapshot-type Recordset object. DAO provides four Find methods:

  • The FindFirst method finds the first record that satisfies the specified criteria.
  • The FindLast method finds the last record that satisfies the specified criteria.
  • The FindNext method finds the next record that satisfies the specified criteria.
  • The FindPrevious method finds the previous record that satisfies the specified criteria.

When you use the Find methods, you specify the search criteria, which is typically an expression that equates a field name with a specific value.

You can locate the matching records in reverse order by finding the last occurrence with the FindLast method and then using the FindPrevious method instead of the FindNext method.

DAO sets the NoMatch property to True whenever a Find method fails and the current record position is undefined. There may be a current record, but there is no way to tell which one. If you want to be able to return to the previous current record following a failed Find method, use a bookmark.

The NoMatch property is False whenever the operation succeeds. In this case, the current record position is the record found by one of the Find methods.

The following example illustrates how you can use the FindNext method to find all orders in the Orders table that have no corresponding records in the Order Details table. The function searches for missing orders and, if it finds one, it adds the value in the OrderID field to the array aryOrders().

  Function FindOrders() As Variant

Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset
Dim rstOrderDetails As DAO.Recordset
Dim strSQL As String
Dim intIndex As Integer
Dim aryOrders() As Long

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   'Open recordsets on the Orders and OrderDetails tables. If there are
   'no records in either table, exit the function.
   strSQL = "SELECT * FROM Orders ORDER BY OrderID"
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
   If rstOrders.EOF Then Exit Function

   strSQL = "SELECT * FROM [Order Details] ORDER BY OrderID"
   Set rstOrderDetails = dbsNorthwind.OpenRecordset(strSQL, _
                         dbOpenSnapshot)

   'For the first record in Orders, find the first matching record
   'in OrderDetails. If no match, redimension the array of order IDs and
   'add the order ID to the array.
   intIndex = 1
   rstOrderDetails.FindFirst "OrderID = " & rstOrders![OrderID]
   If rstOrderDetails.NoMatch Then
      ReDim Preserve aryOrders (1 To intIndex)
      aryOrders (intIndex) = rstOrders![OrderID]
      rstOrders.MoveNext
   End If

   'The first match has already been found, so use the FindNext method to
   'find the next record that satisfies the criteria.
   Do Until rstOrders.EOF
      rstOrderDetails.FindNext "OrderID = " & rstOrders![OrderID]
      If rstOrderDetails.NoMatch Then
         intIndex = intIndex + 1
         ReDim Preserve aryOrders (1 To intIndex)
         aryOrders (intIndex) = rstOrders![OrderID]
      End If
      rstOrders.MoveNext
   Loop

   FindOrders = aryOrders

   rstOrders.Close
   rstOrderDetails.Close
   dbsNorthwind.Close

   Set rstOrders = Nothing
   Set rstOrderDetails = Nothing
   Set dbsNorthwind = Nothing

Exit Function

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function