ACC1x: How to Get Record Position Number from Record Selector

• 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:

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

         Requery
            ControlName: Record Position
   
   

2. Open the form in Design view. Make sure that the Form Hdr/Ftr option is selected on the Layout menu.

3. Create a text box in the footer with the following properties:

         Name: Record Position (ControlName: Record Position in version 1.x)
         ControlSource: =GetRecordPosition()
   

4. 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.