FindFirst, FindLast, FindNext, FindPrevious Methods

Applies To Dynaset-Type Recordset object, Recordset object, Snapshot-Type Recordset object.

Description

Locates the first, last, next, or previous record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Jet workspaces only).

Syntax

recordset.{FindFirst | FindLast | FindNext | FindPrevious} criteria

The Find methods have these parts.

Part	Description

recordset	An object variable that represents an existing dynaset- or snapshot-type Recordset object.
criteria	A String used to locate the record. It is like the WHERE clause in an SQL statement, but without the word WHERE.

Remarks If you want to include all the records in your search — not just those that meet a specific condition — use the Move methods to move from record to record. To locate a record in a table-type Recordset, use the Seek method.

If a record matching the criteria isn't located, the current record pointer is unknown, and the NoMatch property is set to True. If recordset contains more than one record that satisfies the criteria, FindFirst locates the first occurrence, FindNext locates the next occurrence, and so on.

Each of the Find methods begins its search from the location and in the direction specified in the following table.

Find method	Begins searching at	Search direction

FindFirst	Beginning of recordset	End of recordset
FindLast	End of recordset	Beginning of recordset
FindNext	Current record	End of recordset
FindPrevious	Current record	Beginning of recordset

When you use the FindLast method, the Microsoft Jet database engine fully populates your Recordset before beginning the search, if this hasn't already happened.

Using one of the Find methods isn't the same as using a Move method, however, which simply makes the first, last, next, or previous record current without specifying a condition. You can follow a Find operation with a Move operation.

Always check the value of the NoMatch property to determine whether the Find operation has succeeded. If the search succeeds, NoMatch is False. If it fails, NoMatch is True and the current record isn't defined. In this case, you must position the current record pointer back to a valid record.

Using the Find methods with Microsoft Jet-connected ODBC -accessed recordsets can be inefficient. You may find that rephrasing your criteria to locate a specific record is faster, especially when working with large recordsets.

In an ODBCDirect workspace, the Find and Seek methods are not available on any type of Recordset object, because executing a Find or Seek through an ODBC connection is not very efficient over the network. Instead, you should design the query (that is, using the source argument to the OpenRecordset method) with an appropriate WHERE clause that restricts the returned records to only those that meet the criteria you would otherwise use in a Find or Seek method.

When working with Microsoft Jet-connected ODBC databases and large dynaset-type Recordset objects, you might discover that using the Find methods or using the Sort or Filter property is slow. To improve performance, use SQL queries with customized ORDER BY or WHERE clauses, parameter queries, or QueryDef objects that retrieve specific indexed records.

You should use the U.S. date format (month-day-year) when you search for fields containing dates, even if you're not using the U.S. version of the Microsoft Jet database engine; otherwise, the data may not be found. Use the Visual Basic Format function to convert the date. For example:

rstEmployees.FindFirst "HireDate > #" _
    & Format(mydate, 'm-d-yy' ) & "#"

If criteria is composed of a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to call the method. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Jet SQL only accepts U.S. decimal characters.

Notes

For best performance, the criteria should be in either the form "field = value" where field is an indexed field in the underlying base table, or "field LIKE prefix" where field is an indexed field in the underlying base table and prefix is a prefix search string (for example, "ART*").
In general, for equivalent types of searches, the Seek method provides better performance than the Find methods. This assumes that table-type Recordset objects alone can satisfy your needs.

See Also

AbsolutePosition property, Move method, MoveFirst, MoveLast, MoveNext, MovePrevious methods, NoMatch property, Seek method.

Specifics (Microsoft Access)

When specifying criteria for the Find methods, you must be careful to reference fields and controls properly, and to construct the criteria string correctly. For more information on restricting data to a subset of records and quotation marks in strings, see Domain Aggregate Functions.

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

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

Example (Microsoft Access)

The following example creates a dynaset-type Recordset object and then uses the FindFirst method to locate the first record satisfying the specified criteria. The procedure then finds the remaining records that satisfy the criteria.

Sub FindRecord()
    Dim dbs As Database, rst As Recordset
    Dim strCriteria As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Define search criteria.
    strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"
    ' Create a dynaset-type Recordset object based on Orders table.
    Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
    ' Find first matching record.
    rst.FindFirst strCriteria
    ' Check if record is found.
    If rst.NoMatch Then
        MsgBox "No record found."
    Else
        ' Find other matching records.
        Do Until rst.NoMatch
            Debug.Print rst!ShipCountry; "   "; rst!OrderDate
            rst.FindNext strCriteria
        Loop
    End If
    rst.Close
    Set dbs = Nothing
End Sub