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

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.

Additional query words: vcr

Keywords : kbprg
Issue type : kbhowto
Technology :

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