Move Method

Applies To

Dynaset-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	The name of 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).
start	A String value identifying a bookmark. If start is specified, the move begins relative to this bookmark. If start is not specified, Move begins from the current record.

Remarks

If using Move would move the position of the current record to a position before the first record, the position is moved to the beginning-of-file (BOF) position. If the Recordset contains no records and its BOF property is set to True, using this method to move backward produces a trappable run-time error. If either the BOF or EOF property is True and you attempt to use the Move method without a valid bookmark, a trappable error is triggered.

If using Move would move the position of the current record to a position after the last record, the position is moved to the end-of-file (EOF) position. If the Recordset is based on a query, then this approach forces the query to be run on the specified number of rows. If the Recordset contains no records and its EOF property is set to True, then using this method to move forward produces a trappable run-time error.

Notes

When you use Move on a forward-only scrolling snapshot, 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 the MoveFirst, MoveLast, MoveNext, or MovePrevious method.

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 creates a function that moves the current record position in a Recordset forward or back a specified number of records and provides an optional offset.

Sub MoveRelative (lngDistance As Long, strDirection As String, _
    rstTarget As Recordset, varRelativeMark As Variant)
    Dim strHere As String
    strHere = rstTarget.Bookmark
    strDirection = UCase(strDirection)
    Select Case strDirection
        Case "FWD":  lngDistance = Abs(lngDistance)
        Case "BACK": lngDistance = Abs(lngDistance) * -1
        Case Else: MsgBox "Incorrect calling argument"
    End Select
    If UCase(varRelativeMark) = "HERE" Then

        rstTarget.Move lngDistance    ' Move current record position.
    Else
        ' Move relative to bookmark.
        rstTarget.Move lngDistance, varRelativeMark
    End If
    ' Move may not have been completed.
    If rstTarget.EOF Or rstTarget.BOF Then Beep
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 Database variable pointing to current database.
    Set dbs = CurrentDb
    ' Open Dynaset-Type Recordset object.
    Set rst = dbs.OpenRecordset("SELECT * FROM Orders " & _
        "ORDER BY [ShipCountry];")
    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
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 onto 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