Finding the Current Position in a Recordset

In some situations, you need to determine how far through a Recordset object you have moved the current record position, and perhaps indicate the current record position to a user. For example, you may want to indicate the current position on a dial, meter, or similar type of control. Two properties are available to indicate the current position: the AbsolutePosition property and the PercentPosition property.

The AbsolutePosition property value is the position of the current record from 0 to one less than the total number of records in a non-table-type Recordset object. However, don’t think of this property as a record number; if the current record is undefined, the AbsolutePosition property returns  – 1. Further, there is no guarantee that records will appear in the same order every time the Recordset object is accessed.

The PercentPosition property shows the current position expressed as a percentage of the total number of records indicated by the RecordCount property. Because the RecordCount property does not reflect the total number of records in the Recordset object until the Recordset object has been fully populated, the PercentPosition property reflects the current record position only as a percentage of the number of records that have been accessed since the Recordset object was opened. To make sure that the PercentPosition property reflects the current record position relative to the entire Recordset object, use the MoveLast and MoveFirst methods immediately after opening the Recordset object. This fully populates the Recordset object before you attempt to use the PercentPosition property. If you have a large result set, you may not want to use the MoveLast method because it may take a long time.

Caution The PercentPosition property is only an approximation and shouldn’t be used as a critical parameter. This property is best suited for driving an indicator, such as a “percent complete” control, or a similar indicator that marks a user’s progress while moving though a set of records.

The following example opens a Recordset object on a table called Employees and fully populates it. The program then lists the content of each field of each record in the table, numbering the records as they are printed. When half the records have been printed, a message indicates that the user is more than halfway through the table. 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", dbOpenDynaset)

With rst
	' Move to end of recordset.
	.MoveLast
	' Move back to start of recordset.
	.MoveFirst
	Do While Not .EOF
		Debug.Print "Record No. " & .AbsolutePosition + 1
		Debug.Print !FirstName & " " & !LastName
		Debug.Print "AbsolutePosition = " & .AbsolutePosition
		Debug.Print "PercentPosition = " & .PercentPosition
		If .PercentPosition > 50 Then
			Debug.Print "More than half way!"
		End If
		Debug.Print
		.MoveNext
	Loop
	.Close
End With