How to: Find the Current Position in a DAO Recordset

  • Article
Access Developer Reference

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 relative to 0. However, do not think of this property as a record number; if the current record is undefined, the AbsolutePosition property returns 1. In addition, there is no assurance that a record will have the same absolute position if the Recordset object is recreated because the order of individual records within a Recordset object is not guaranteed unless it is created with an SQL statement that includes an ORDER BY clause.

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 has been fully populated, the PercentPosition property reflects only the current record position as a percentage of the number of records that have been accessed since the Recordset was opened. To make sure that the PercentPosition property reflects the current record position relative to the entire Recordset, use the MoveLast and MoveFirst methods immediately after opening the Recordset. This fully populates the Recordset object before you use the PercentPosition property. If you have a large result set, using the MoveLast method may take a long time for Recordsets that are not of type table.

Bb243792.vs_note(en-us,office.12).gif  Note
The PercentPosition property is only an approximation and should not be used as a critical parameter. This property is best suited for driving an indicator that marks a user's progress while moving though a set of records. For example, you may want a control that indicates the percentage of records completed.

The following example opens a Recordset object on a table called Employees. The procedure then moves through the Employees table and uses the SysCmd method to display a progress bar showing the percentage of the table that has been processed. If the hire date of the employee is before Jan. 1, 1993, the text "Senior Staff" is appended to the Notes field.

  Sub AddEmployeeNotes()

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strMsg As String
Dim intRet As Integer
Dim intCount As Integer
Dim strSQL As String
Dim sngPercent As Single
Dim varReturn As Variant
Dim lngEmpID() As Long


On Error GoTo ErrorHandler


Set dbsNorthwind = CurrentDb


strSQL = "SELECT * FROM Employees"
Set rstEmployees = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)


With rstEmployees
If .EOF Then            ' If no records, exit.
Exit Sub
Else
strMsg = "Processing Employees table..."
intRet = SysCmd(acSysCmdInitMeter, strMsg, 100)
End If


  Do Until .EOF
     If !HireDate < #1/1/93# Then
        .Edit
        !Notes = !Notes & ";" & "Senior Staff"
        .Update
     End If

     If .PercentPosition <> 0 Then
        intRet = SysCmd(acSysCmdUpdateMeter, .PercentPosition)
     End If
     .MoveNext
  Loop



End With


intRet = SysCmd(acSysCmdRemoveMeter)


rstEmployees.Close
dbsNorthwind.Close


Set rstEmployees = Nothing
Set dbsNorthwind = Nothing


Exit Sub


ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
varReturn = SysCmd(acSysCmdSetStatus, " ")
End Sub