AbsolutePosition Property

Applies To

Dynaset-Type Recordset Object, Recordset Object, Snapshot-Type Recordset Object.

Description

Sets or returns the relative record number of a Recordset object's current record.

Settings and Return Values

The setting or return value is an integer from 0 to one less than the number of records in the Recordset object. It corresponds to the ordinal position of the current record in the Recordset object specified by the object. The data type is Long.

Remarks

The AbsolutePosition property enables you to position the current record pointer to a specific record based on its ordinal position in a dynaset- or snapshot-type Recordset object. You can also determine the current record number by checking the AbsolutePosition property setting.

Because the AbsolutePosition property value is zero-based (that is, a setting of 0 refers to the first record in the Recordset object), it cannot be set to a value greater than or equal to the number of populated records; doing so causes a trappable error. You can determine the number of populated records in the Recordset object by checking the RecordCount property setting. The maximum allowable setting for the AbsolutePosition property is the value of the RecordCount property minus 1.

If there is no current record, as when there are no records in the Recordset object, –1 is returned. If the current record is deleted, the AbsolutePosition property value isn't defined, and a trappable error occurs if it's referenced. New records are added to the end of the sequence.

This property isn't intended to be used as a surrogate record number. Bookmarks are still the recommended way of retaining and returning to a given position and are the only way to position the current record across all types of Recordset objects. In particular, the position of a given record changes when one or more records preceding it are deleted. There is also no assurance that a given record will have the same absolute position if the Recordset object is re-created again because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement using an ORDER BY clause.

Note

The AbsolutePosition property isn't available on a forward-only scrolling snapshot type Recordset object, or on a Recordset object opened from a pass-through query against a remote database.

See Also

PercentPosition Property, RecordCount Property.

Example

This example positions the current record to the nth record in a dynaset-type Recordset object.

Function MoveToRecord (intPosition As Long, rstTest As recordset) _
    As Integer
    rstTest.MoveLast
    If rstTest.RecordCount > intPosition Then
        rstTest.AbsolutePosition = intPosition
        MoveToRecord = True
    Else
        MoveToRecord = False
    End If
End Function

Example (Microsoft Access)

The following example uses the AbsolutePosition property to set the ordinal position of the first record in a dynaset-type Recordset object to 1.

Sub FirstRecord()
    Dim dbs As Database, rst As Recordset

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Orders WHERE " & _
        "[ShippedDate] >= #1-1-95#;")
    rst.MoveFirst
    rst.AbsolutePosition = 1
End Sub

Example (Microsoft Excel)

This example prompts the user for a record number. The example uses this number to move to a record in the Customer recordset in the NWINDEX.MDB database, and then it copies the values for three specified fields onto Sheet1.

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

Dim db As Database, rs As Recordset
Sheets("Sheet1").Activate
recordNumber = Application.InputBox(Prompt:="Record number to copy " _
    & "to Sheet1", Title:="Record to copy", Type:=1)
If recordNumber = False Then    ' user cancelled InputBox
    Exit Sub
End If
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer", dbOpenSnapshot)
rs.MoveLast
If rs.RecordCount > recordNumber Then
    rs.AbsolutePosition = recordNumber
    ActiveCell.Value = rs.Fields("CONTACT").Value
    ActiveCell.Offset(, 1).Value = rs.Fields("ADDRESS").Value
    ActiveCell.Offset(, 2).Value = rs.Fields("CITY").Value

Else
    MsgBox "The record #" & recordNumber & " doesn't exist."
End If
rs.Close
db.Close