PercentPosition Property

Applies To

Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object.

Description

Sets or returns a value that indicates or changes the approximate location of the current record in the Recordset object based on a percentage of the records in the Recordset.

Settings and Return Values

The setting or return value is a number between 0.0 and 100.00. The data type is Single.

Remarks

To indicate or change the approximate position of the current record in a Recordset object, you can check or set the PercentPosition property. When working with a dynaset- or Snapshot-Type Recordset object, first populate the Recordset object by moving to the last record before you set or check the PercentPosition property. If you use the PercentPosition property before fully populating the Recordset object, the amount of movement is relative to the number of records accessed as indicated by the RecordCount property setting. You can move to the last record using MoveLast method.

Note

Using the PercentPosition property to move the current record to a specific record in a Recordset object isn't recommended — the Bookmark property is better suited for this task.

Once you set the PercentPosition property to a value, the record at the approximate position corresponding to that value becomes current, and the PercentPosition property is reset to a value that reflects the approximate position of the current record. For example, if your Recordset object contains only five records, and you set its PercentPosition property value to 77, the value returned from the PercentPosition property may be 80, not 77.

The PercentPosition property applies to all three types of Recordset objects, including tables without indexes. The only exceptions are that it does not apply to forward-only scrolling snapshots), or on a Recordset object opened from a pass-through query against a remote database.

You can use the PercentPosition property with a scroll bar on a form or text box to indicate the location of the current record in a Recordset object.

See Also

Bookmark Property; Index Property; Move Method; MoveFirst, MoveLast, MoveNext, MovePrevious Methods.

Example

This example displays the current position of a record in a Recordset in a message box.

Dim dbsNorthwind As Database, rstEmployees As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Open table.
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
' Set current index.
rstEmployees.Index = "Last Name"    
...
...    ' Move among the records.
...
' Display current position.
MsgBox("Current position is " & rstEmployees.PercentPosition & " %.")

Example (Microsoft Access)

The following example expresses the current position of a record in a recordset as a percent, then displays it in a message box.

Sub PercentOfRecords()
    Dim dbs As Database, rstProducts As Recordset
    Dim strInput As String, strSQL As String

    strSQL = "SELECT * FROM Products ORDER BY ProductName;"
    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Open table.
    Set rstProducts = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    ' Populate recordset.
    rstProducts.MoveLast
    ' Return to start of recordset.
    rstProducts.MoveFirst
    ' Prompt user to input name of product.
    strInput = InputBox("Please enter the full product name.")
    ' Find first occurrence.
    rstProducts.FindFirst "[ProductName] = '" & strInput & "'"
    ' Display current position as a percent.
    MsgBox("The current position in the Recordset is " & _
        rstProducts.PercentPosition & "%.")
End Sub

Example (Microsoft Excel)

This example selects and changes records in the Customer recordset in the NWINDEX.MDB database, and then it copies the recordset onto Sheet1. The status bar shows the percentage of records changed.

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 [CON_TITLE], [CONTACT], [COMPANY] FROM Customer " _
    & "WHERE [CON_TITLE] = 'Owner';"
Set rs = db.OpenRecordset(sQLText, dbOpenDynaset)
rs.MoveFirst
Sheets("Sheet1").Activate
Do While Not rs.EOF
    rs.Edit
    rs.Fields("CON_TITLE").Value = "Account Excecutive"
    rs.Update
    Application.StatusBar = rs.PercentPosition & "% of the" _
        & " records have been replaced."
    rs.MoveNext
Loop
Application.StatusBar = "Done"
rs.MoveFirst
numberOfRows = ActiveCell.CopyFromRecordset(rs)
MsgBox numberOfRows & " Records have been changed"
rs.Close
db.Close