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