FindFirst, FindLast, FindNext, FindPrevious Methods Example

This example uses the FindFirst, FindLast, FindNext, and FindPrevious methods to move the record pointer of a Recordset based on the supplied search string and command. The FindAny function is required for this procedure to run.

Sub FindFirstX()

   Dim dbsNorthwind As Database
   Dim rstCustomers As Recordset
   Dim strCountry As String
   Dim varBookmark As Variant
   Dim strMessage As String
   Dim intCommand As Integer

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstCustomers = dbsNorthwind.OpenRecordset( _
      "SELECT CompanyName, City, Country " & _
      "FROM Customers ORDER BY CompanyName", _
      dbOpenSnapshot)

   Do While True
      ' Get user input and build search string.
      strCountry = _
         Trim(InputBox("Enter country for search."))
      If strCountry = "" Then Exit Do
      strCountry = "Country = '" & strCountry & "'"

      With rstCustomers
         ' Populate recordset.
         .MoveLast
         ' Find first record satisfying search string. Exit 
         ' loop if no such record exists.
         .FindFirst strCountry
         If .NoMatch Then
            MsgBox "No records found with " & _
               strCountry & "."
            Exit Do
         End If

         Do While True
            ' Store bookmark of current record.
            varBookmark = .Bookmark
            ' Get user choice of which method to use.
            strMessage = "Company: " & !CompanyName & _
               vbCr & "Location: " & !City & ", " & _
               !Country & vbCr & vbCr & _
               strCountry & vbCr & vbCr & _
               "[1 - FindFirst, 2 - FindLast, " & _
               vbCr & "3 - FindNext, " & _
               "4 - FindPrevious]"
            intCommand = Val(Left(InputBox(strMessage), 1))
            If intCommand < 1 Or intCommand > 4 Then Exit Do

            ' Use selected Find method. If the Find fails, 
            ' return to the last current record.
            If FindAny(intCommand, rstCustomers, _
                  strCountry) = False Then
               .Bookmark = varBookmark
               MsgBox "No match--returning to " & _
                  "current record."
            End If

         Loop

      End With

      Exit Do
   Loop

   rstCustomers.Close
   dbsNorthwind.Close

End Sub

Function FindAny(intChoice As Integer, _
   rstTemp As Recordset, _
   strFind As String) As Boolean

   ' Use Find method based on user input.
   Select Case intChoice
      Case 1
         rstTemp.FindFirst strFind
      Case 2
         rstTemp.FindLast strFind
      Case 3
         rstTemp.FindNext strFind
      Case 4
         rstTemp.FindPrevious strFind
   End Select

   ' Set return value based on NoMatch property.
   FindAny = IIf(rstTemp.NoMatch, False, True)

End Function