You can use the following methods to locate a record in a non-table-type Recordset object. (To locate a record in a table-type Recordset object, use the Seek method, which is described in the previous section.) Microsoft Jet supports four Find methods:
When you use the Find methods, you specify the search criteria, which is typically an expression equating a field name with a specific value. If no record matching the specified criteria is found, the Recordset object’s NoMatch property is set to True.
The following example looks up phone numbers in an Employees table. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset Dim strName As String, strCriteria As String Dim strSQL As String, strInput As String Dim strMsg As String Set dbs = OpenDatabase(strDbPath) strSQL = "SELECT FirstName, LastName, HomePhone " _ & "FROM Employees ORDER BY LastName" ' Open snapshot-type recordset. Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot) strInput = "Enter the first few letters of the last " _ & "name of the employee you want to find: " ' Prompt user for input. strName = InputBox(strInput) ' Check for input. If Len(strName) = 0 Then Exit Sub ' Append asterisk to user's input. strCriteria = "LastName Like """ & strName & "*""" With rst ' Find first instance of criteria. .FindFirst strCriteria Do ' Check for match. If Not .NoMatch Then strMsg = "The number for " & !FirstName & _ " " & !LastName & " is " & _ !HomePhone & ". Search again?" ' Display result and prompt user to search again. If MsgBox(strMsg, vbYesNo) = vbYes Then strName = InputBox(strInput) If Len(strName) = 0 Then Exit Sub strCriteria = "LastName Like """ & strName & "*""" .FindFirst strCriteria Else Exit Sub End If Else strMsg = "There was no match found for " _ & " an employee last name like '" & _ strName & "*'" MsgBox strMsg, vbOKOnly, "Find Home Phone" Exit Sub End If Loop .Close End With dbs.Close Set dbs = Nothing
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.
Microsoft Jet 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 you have 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 (as described in the following section).
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 records in the Customers table that contain a Null value in the Fax field. It then prompts the user to enter a fax number for the customer. In this example, strDbPath is the path to the database:
Dim dbs As Database, rst As Recordset
Dim strCriteria As String, varNewFax As Variant
Dim strMsg As String
Set dbs = OpenDatabase(strDbPath)
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)
' Check for Null value in Fax field.
strCriteria = "Fax Is Null"
With rst
' Find first Null value.
.FindFirst strCriteria
Do Until .NoMatch
' Prompt user to enter fax number.
varNewFax = InputBox(!CompanyName & " is missing " & _
"a fax number. Please enter the fax number now.")
If Len(varNewFax) = 0 Then
strMsg = "Do you want to find " _
& "the next missing fax number?"
If MsgBox(strMsg, vbYesNo, "Continue Search?") = vbNo Then
Exit Sub
Else
varNewFax = Null
End If
End If
' Edit and update record.
.Edit
!Fax = varNewFax
.Update
' Find next Null value.
.FindNext strCriteria
Loop
.Close
End With
If you need to frequently search a large number of records in a dynaset, you may get better performance by creating a temporary indexed table and using the Seek method instead.