Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103260

From BetaArchive Wiki

ACC1x: How to Get Record Position Number from Record Selector


The information in this article applies to:

  • Microsoft Access versions 1.0, 1.1


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.


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
   '   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
           RecordPos = RecordPos + 1

       GetRecordPosition = RecordPos

       Exit Function

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

       Select Case Err
               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:

             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.

Additional query words: vcr

Keywords : kbprg
Issue type : kbhowto
Technology :

Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.