Moving Through a Recordset

A Recordset object has a current position, usually at a record. When you refer to the fields in a Recordset object, you obtain values from the record at the current position, which is known as the current record. However, the current position can also be immediately before the first record in a Recordset object or immediately after the last record. In certain circumstances, the current position is undefined. When you open a Recordset object that contains at least one record, the first record is the current record and the Recordset object’s BOF property is False. If the Recordset object contains no records, the Recordset object’s BOF and EOF properties will both be True. For more information about the BOF and EOF properties, see the section “Detecting the Limits of a Recordset.”

You can use the Move methods to loop through the records in a Recordset object:

• The MoveFirst method moves to the first record.
  
  
• The MoveLast method moves to the last record.
  
  
• The MoveNext method moves to the next record.
  
  
• The MovePrevious method moves to the previous record.
  
  
• The Move rows, start method moves forward or backward the number of records specified by the rows argument, beginning with the record specified by the start argument. The start argument is optional, and if left blank, the Move operation begins at the current record.

The following example changes the job title of all sales representatives in a table called Employees. After opening the table, the code sets the index, which makes it necessary to use the MoveFirst method to locate the first record. For each record satisfying the title condition, the example changes the title and saves the change with the Update method. It uses the MoveNext method to move to the next record. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)

With rst
	' Set current index.
	.Index = "LastName"
	' Locate first record.
	.MoveFirst
	' Begin loop.
	Do Until .EOF
		If !Title = "Sales Representative" Then
			' Enable editing.
			.Edit
			' Change title.
			!Title = "Account Executive"
			' Save changes.
			.Update
		End If
	' Locate next record.
	.MoveNext
	' End of loop.
	Loop
	' Close recordset.
	.Close
End With

Visual Basic Users You can use the Data control to browse and display records in a Recordset object and to edit data in records as they are displayed. For more information on the Data control, see the Microsoft Visual Basic Programmer’s Guide.