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.

Syntax

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

The Find methods have these parts.

Part	Description

recordset	The name of an existing dynaset- or Snapshot-Type Recordset object.
criteria	A string expression (like the WHERE clause in an SQL statement without the word WHERE) used to locate the record.

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 undetermined, 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.

Caution

If you edit the current record, be sure you save the changes using the Update method before you move to another record. If you move to another record without updating, your changes are lost without warning.

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

Find methods	Begin	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 been done.

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 set to False. If it fails, NoMatch is set to True and the current record isn't defined. In this case, you must position the current record pointer back to a valid record, as shown in the following example.

Dim Here as Variant
Here = rstCustomers.Bookmark
rstCustomers.Find "Name = 'Ms. Schmidt'"    'Search for a name in the 
                    ' recordset.
If rstCustomers.NoMatch then     'Was it found?
    rstCustomers.Bookmark = Here    'If not, go back to 
                    ' previously current record.
Else
.                     'Match found.
...
...
End If

Using the Find methods with 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.

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 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' ) & "#"

When working with ODBC databases and large dynasets, you might discover that using the Find methods or using the Sort or Filter properties is slow. You can improve performance by using SQL queries with customized ORDER BY or WHERE clauses, parameter queries, or QueryDef objects that retrieve specific indexed records.

Note

You can't use the Find methods with forward-only-scrolling snapshots.

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.

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.

You should use the the month-day-year format when you search for fields containing dates, even if the Regional Settings of the Windows® Control Panel is set to a different format; otherwise, the data may not be found. You can use the Visual Basic Format function to make this easier. The following code requests a date from the user and constructs a string on which to search.

dim dteAny As Date
dteAny = InputBox ("Please enter a date in month-day-year format.")
rstOrders.FindFirst "[OrderDate] > #" & Format(dteAny, "m/d/yy") & "#"

Example

This example uses FindFirst to find the first record matching the specified criteria in a Recordset object and then uses FindNext to find the next matching record.

Dim strCriteria As String
strCriteria = "State = 'NY'"    ' Set the criteria.
rstAddress.FindFirst strCriteria    ' Find first matching record.
If rstAddress.NoMatch = False Then    ' Check if record is found.
    rstAddress.FindNext strCriteria    ' Find next matching record.
...

This example creates a dynaset-type Recordset object and then uses FindFirst to locate the first record satisfying the title condition.

Dim strCriteria As String
Dim dbsBiblio As Database, rstPublishers As Recordset
strCriteria = "State = 'NY'"     ' Define search criteria.
Set dbsBiblio = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
' Create a dynaset-type Recordset based on Publishers table.
Set rstPublishers = dbsBiblio.OpenRecordset("Publishers",dbOpenDynaset)
rstPublishers.FindFirst strCriteria    ' Find first matching record.
If rstPublishers.NoMatch = False Then    ' Check if record is found.
    rstPublishers.FindNext strCriteria    ' Find next matching record.
...

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 Database variable pointing 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
End Sub