Move Method

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

Moves the position of the current record in a Recordset object.

Syntax

recordset.Move rows, start

The Move method syntax has these parts.

Part	Description

recordset	An object variable that represents the Recordset object whose current record position is being moved.
rows	A signed Long value specifying the number of rows the position will move. If rows is greater than 0, the position is moved forward (toward the end of the file). If rows is less than 0, the position is moved backward (toward the beginning of the file).
startbookmark	Optional. A Variant (String subtype) value identifying a bookmark. If you specify startbookmark, the move begins relative to this bookmark. Otherwise, Move begins from the current record.

Remarks If you use Move to position the current record pointer before the first record, the current record pointer moves to the beginning of the file. If the Recordset contains no records and its BOF property is True, using this method to move backward causes an error.

If you use Move to position the current record pointer after the last record, the current record pointer position moves to the end of the file. If the Recordset contains no records and its EOF property is True, then using this method to move forward causes an error.

If either the BOF or EOF property is True and you attempt to use the Move method without a valid bookmark, a run-time error occurs.

Notes

When you use Move on a forward-only-type Recordset object, the rows argument must be a positive integer and bookmarks aren't allowed. This means you can only move forward.
To make the first, last, next, or previous record in a Recordset the current record, use either the MoveFirst, MoveLast, MoveNext, or MovePrevious method.
Using Move with rows equal to 0 is an easy way to retrieve the underlying data for the current record. This is useful if you want to make sure that the current record has the most recent data from the base tables. It will also cancel any pending Edit or AddNew calls.

See Also

BOF, EOF properties, Bookmark property, MoveFirst, MoveLast, MoveNext, MovePrevious methods.

Specifics (Microsoft Access)

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 Move method to position the record pointer based on user input.

Sub MoveX()

    Dim dbsNorthwind As Database
    Dim rstSuppliers As Recordset
    Dim varBookmark As Variant
    Dim strCommand As String
    Dim lngMove As Long

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstSuppliers = _
        dbsNorthwind.OpenRecordset("SELECT CompanyName, " & _
        "City, Country FROM Suppliers ORDER BY CompanyName", _
        dbOpenDynaset)

    With rstSuppliers
        ' Populate recordset.
        .MoveLast
        .MoveFirst

        Do While True
            ' Display information about current record and ask
            ' how many records to move.
            strCommand = InputBox( _
                "Record " & (.AbsolutePosition + 1) & " of " & _
                .RecordCount & vbCr & "Company: " & _
                !CompanyName & vbCr & "Location: " & !City & _
                ", " & !Country & vbCr & vbCr & _
                "Enter number of records to Move " & _
                "(positive or negative).")

            If strCommand = "" Then Exit Do

            ' Store bookmark in case the Move doesn't work.
            varBookmark = .Bookmark

            ' Move method requires parameter of data type Long.
            lngMove = CLng(strCommand)
            .Move lngMove

            ' Trap for BOF or EOF.
            If .BOF Then
                MsgBox "Too far backward! " & _
                    "Returning to current record."
                .Bookmark = varBookmark
            End If
            If .EOF Then
                MsgBox "Too far forward! " & _
                    "Returning to current record."
                .Bookmark = varBookmark
            End If
        Loop
        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example uses the Move method to move forward two rows in a Recordset object:

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

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset("SELECT * FROM Orders " _
        & "ORDER BY ShipCountry;")
    rst.MoveLast
    rst.MoveFirst
    ' Check number of records in Recordset object.
    If rst.RecordCount > 2 Then
        ' Move forward two rows.
        rst.Move 2
        Debug.Print rst!ShipCountry
    End If
    rst.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example prompts the user to select a record number. The example then copies the selected record from the Customer recordset in the Nwindex.mdb database to Sheet1.

Dim db As Database, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("SELECT [CUSTMR_ID], [CONTACT], [REGION] " _
    & "FROM Customer")
Sheets("Sheet1").Activate
aDistance = Application.InputBox("What record # you want to copy", _
    Type:=2)
If aDistance = False Then    ' user cancelled InputBox
    Exit Sub
End If
rs.MoveFirst
rs.Move aDistance
For i = 0 To 2
    ActiveCell.Offset(, i).Value = rs.Fields(i).Value
Next
rs.Close
db.Close