MoveFirst, MoveLast, MoveNext, MovePrevious Methods

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Move to the first, last, next, or previous record in a specified Recordset object and make that record the current record.

Syntax

recordset.{MoveFirst | MoveLast [dbRunAsync] | MoveNext | MovePrevious}

The recordset placeholder is an object variable that represents an open Recordset object.

Remarks

Use the Move methods to move from record to record without applying a condition.

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

When you open a Recordset, the first record is current and the BOF property is False. If the Recordset contains no records, the BOF property is True, and there is no current record.

If the first or last record is already current when you use MoveFirst or MoveLast, the current record doesn't change.

If you use MovePrevious when the first record is current, the BOF property is True, and there is no current record. If you use MovePrevious again, an error occurs, and BOF remains True.

If you use MoveNext when the last record is current, the EOF property is True, and there is no current record. If you use MoveNext again, an error occurs, and EOF remains True.

If recordset refers to a table-type Recordset (Microsoft Jet workspaces only), movement follows the current index. You can set the current index by using the Index property. If you don't set the current index, the order of returned records is undefined.

Important You can use the MoveLast method to fully populate a dynaset- or snapshot-type Recordset to provide the current number of records in the Recordset. However, if you use MoveLast in this way, you can slow down your application's performance. You should only use MoveLast to get a record count if it is absolutely necessary to obtain an accurate record count on a newly opened Recordset. If you use the dbRunAsync constant with MoveLast, the method call is asynchronous. You can use the StillExecuting property to determine when the Recordset is fully populated, and you can use the Cancel method to terminate execution of the asynchronous MoveLast method call.

You can't use the MoveFirst, MoveLast, and MovePrevious methods on a forward-only–type Recordset object.

To move the position of the current record in a Recordset object a specific number of records forward or backward, use the Move method.

See Also AbsolutePosition property, BOF, EOF properties, Cancel method, FindFirst, FindLast, FindNext, FindPrevious methods, Index property, Move method, RecordCount property, Seek method, StillExecuting property.

Example

This example uses the MoveFirst, MoveLast, MoveNext, and MovePrevious methods to move the record pointer of a Recordset based on the supplied command. The MoveAny procedure is required for this procedure to run.

Sub MoveFirstX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strMessage As String
    Dim intCommand As Integer

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = dbsNorthwind.OpenRecordset( _
        "SELECT FirstName, LastName FROM Employees " & _
        "ORDER BY LastName", dbOpenSnapshot)

    With rstEmployees
        ' Populate Recordset.
        .MoveLast
        .MoveFirst
        Do While True
            ' Show current record information and get user's
            ' method choice.
            strMessage = "Name: " & !FirstName & " " & _
                !LastName & vbCr & "Record " & _
                (.AbsolutePosition + 1) & " of " & _
                .RecordCount & vbCr & vbCr & _
                "[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
                "3 - MoveNext, 4 - MovePrevious]"
            intCommand = Val(Left(InputBox(strMessage), 1))
            If intCommand < 1 Or intCommand > 4 Then Exit Do
            ' Call method based on user's input.
            MoveAny intCommand, rstEmployees
        Loop
        .Close
    End With

    dbsNorthwind.Close

End Sub

Sub MoveAny(intChoice As Integer, _
    rstTemp As Recordset)

    ' Use specified method, trapping for BOF and EOF.
    With rstTemp
        Select Case intChoice
            Case 1
                .MoveFirst
            Case 2
                .MoveLast
            Case 3
                .MoveNext
                If .EOF Then
                    MsgBox "Already at end of recordset!"
                    .MoveLast
                End If
            Case 4
                .MovePrevious
                If .BOF Then
                    MsgBox "Already at beginning of recordset!"
                    .MoveFirst
                End If
        End Select
    End With

End Sub

Example (Microsoft Access)

The following example uses the MoveLast method to populate the Recordset object so the number of records can be counted. The MoveFirst method then moves the current record pointer to the first record in the Recordset object. The procedure prompts the user to enter a number, then moves that number of records forward.

Sub MoveThroughRecords()
    Dim dbs As Database, rst As Recordset, intI As Integer
    Dim strNumber As String
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Products")
    ' Populate Recordset object.
    rst.MoveLast
    ' Return to first record.
    rst.MoveFirst
    ' Get number smaller than number of records.
    strNumber = InputBox("Please enter a number less than " _
        & rst.RecordCount & ".")
    ' Move specified number of records.
    For intI = 1 To strNumber
        rst.MoveNext
    Next intI
    Debug.Print rst!ProductName
    rst.Close
    Set dbs = Nothing
End Sub