MoveFirst, MoveLast, MoveNext, MovePrevious Methods

Applies To

Dynaset-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

object.{MoveFirst | MoveLast | MoveNext | MovePrevious}

The object placeholder represents an object expression that evaluates to the name of an open Recordset object.

Remarks

Use the Move methods to move from record to record without applying a condition. Use the Find methods to locate records in a dynaset- or Snapshot-Type Recordset object that satisfy a certain condition. To locate a record in a Table-Type Recordset object, use the Seek method.

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.

When you open the recordset named by recordset, the first record is current and the BOF property is set to False. If the recordset contains no records, the BOF property is set to 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 set to True, and there is no current record. If you use MovePrevious again, an error occurs; BOF remains True.

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

If recordset refers to a Table-Type Recordset, movement follows the current index. You can set the current index using the Index property. If you don't set the current index, the order of returned records is undefined.

If you use MoveLast on a Recordset object based on an SQL query or QueryDef, the query is forced to completion and the Recordset object is fully populated.

Notes

You can't use the MoveFirst, MoveLast, or MovePrevious methods with a forward-only scrolling snapshot.
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; FindFirst, FindLast, FindNext, FindPrevious Methods; Index Property; Move Method; RecordCount Property; Seek Method.

Example

This example changes the job title of all sales representatives in a table named Employees. After opening the table, it uses MoveFirst to locate the first record and MoveNext to move to the next record. For each record satisfying the title condition, the example changes the title and saves the change using the Update method.

It's far more efficient to use the WHERE clause of an SQL statement to request the records you want to update than to test each record one at a time. The whole operation could be accomplished by using a single UPDATE statement, as shown in the following example.

Dim dbsNorthwind As Database, rstEmployees As Recordset
Dim strSelect as String
Set dbsNorthwind = DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
strSelect = "Select * FROM Employees WHERE Title = 'Sales Representative' "
' Open recordset.
Set rstEmployees = dbsNorthwind.OpenRecordset(strSelect)
Do Until rstEmployees.EOF    ' Begin loop.
    With rstEmployees
    .Edit    ' Enable editing.
    !Title = "Account Executive"    ' Change title.
    .Update    ' Save changes.
    .MoveNext    ' Locate next record.
    End With
Loop    ' End of loop.
rstEmployees.Close    ' Close table.
dbsNorthwind.Close

Tip

Using an update query to change job titles might be more efficient. For example, you could use the following code to achieve the same results:

Dim strSelect as String
strSelect = "Update Employees Set Title = 'Account Executive' " _
    "WHERE Title = 'Sales Representative' "
dbsNorthwind.Execute strSelect

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 record number, then sets a bookmark for that record.

Sub MoveToRecord()
    Dim dbs As Database, rst As Recordset, intI As Integer
    Dim strNumber As String, strBookmark As String
    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Orders")
    ' Populate Recordset object.
    rst.MoveLast
    ' Return to first record.
    rst.MoveFirst
    strNumber = InputBox("Please enter record number.")
    ' Check that number is within Recordset object.
    If strNumber <= rst.RecordCount And rst.Bookmarkable Then
        For intI = 1 To strNumber
            rst.MoveNext
        Next intI
        strBookmark = rst.Bookmark
    End If
End Sub

Example (Microsoft Excel)

This example replaces values in the CON_TITLE field of the records in the Customer recordset in the NWINDEX.MDB database, and then it displays how many replacements were made.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset, sQLText As String
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
sQLText = "SELECT * FROM  Customer WHERE " _
    & "CON_TITLE =  'Sales Representative';"
Set rs = db.OpenRecordset(sQLText)
i = 0
Do Until rs.EOF
    With rs
        .Edit
        .Fields("CON_TITLE").Value = "Account Executive"
        .Update
        .MoveNext
    End With
    i = i + 1
Loop
MsgBox i & " replacements were made."
rs.Close
db.Close