Microsoft KB Archive/103260

{|
 * width="100%"|

ACC1x: How to Get Record Position Number from Record Selector

 * }

Q103260

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 1.1

-

SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Access does not provide a way to retrieve the position number found in the record selector at the bottom of the form or datasheet.

This article demonstrates a sample user-defined Access Basic function called GetRecordPosition that you can use to retrieve the current record position. This article also explains how to use the GetRecordPosition function to create your own custom record position box on a form.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x.

MORE INFORMATION
The number that appears in the record selector is actually not a record number. It is a number used to indicate the record's relative position in the current record set, not the record's natural position in the table.

The following Access Basic function, GetRecordPosition, can be used to get the record's relative position in the record set. Add this function to a new or existing module.

  'Global Declaration Section Option Explicit

'***************************************************************  ' FUNCTION: GetRecordPosition '  ' PURPOSE: '  Gets the number representing the relative position of the '  record in the currently active form. This is the number that '  appears in the record selector found at the bottom of a form '  or datasheet. '  ' RETURN: '  The relative record position. If there are no records in  '   the active form's dynaset, or if there is no active form, '  Null is returned. '

'***************************************************************  Function GetRecordPosition Const ERR_NO_CURRENT_RECORD = 3021 Dim DS As Dynaset Dim RecordPos As Long

On Error GoTo Err_GetRecordPosition

Set DS = Screen.ActiveForm.Dynaset DS.Bookmark = Screen.ActiveForm.Bookmark RecordPos = 0

While Not DS.BOF DS.MovePrevious RecordPos = RecordPos + 1 Wend

GetRecordPosition = RecordPos

Bye_GetRecordPosition: Exit Function

NoCurrentRec_GetRecordPosition: On Error Resume Next DS.MoveLast If Err <> 0 Then ' There must be no records in the set, so new record is 1. GetRecordPosition = 1 Else ' We are on last record of set, so count + 1. GetRecordPosition = DS.RecordCount + 1 End If      GoTo Bye_GetRecordPosition

Err_GetRecordPosition: Select Case Err Case ERR_NO_CURRENT_RECORD Resume NoCurrentRec_GetRecordPosition Case Else Resume Bye_GetRecordPosition End Select End Function

Creating a Record Position Box on a Form
Use the following steps to create your own custom record position box on a form using the GetRecordPosition function:

  Create a new macro called Refresh Record Position with the following macro action:

     Requery ControlName: Record Position  Open the form in Design view. Make sure that the Form Hdr/Ftr option is selected on the Layout menu.  Create a text box in the footer with the following properties:

     Name: Record Position (ControlName: Record Position in version 1.x)      ControlSource: =GetRecordPosition   Set the following properties on the form (not on the control):

     OnCurrent: Refresh Record Position AfterUpdate: Refresh Record Position

The current record position will be displayed in the text box in the footer of the form either when you navigate to a different record or when a new record is created.

NOTE: The AfterUpdate property of the form is set in the event a new record is created and written to disk. The current record position may need to account for this new record. 

Additional query words: vcr

Keywords : kbprg

Issue type : kbhowto

Technology :