ACC2: How to Get "X of Y" from Record Navigation Buttons

This article demonstrates how to create and use a sample user-defined Access Basic function called GetXofY(). You can use this function to retrieve the record position number (X) and the total number of records (Y) that are displayed in the navigation buttons at the bottom of 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 about Access Basic, please refer to the "Building Applications" manual.

NOTE: This technique uses the undocumented recordset property AbsolutePosition which is subject to change in future versions of Microsoft Access. Use of this function is not supported by Microsoft.

The record position number (X) in the navigation buttons does not indicate the actual record number. Rather, it indicates the record's position in the current record set relative to the total number of records (Y). For example, the fifth record in a table may not have record position number 5 if the records are sorted or filtered in a different order in the form.

You can use the GetXofY() function to return a string that contains the current record position number (X) and the total number of records (Y).

How to Create the GetXofY() Function in a Form

1. Open the sample database NWIND.MDB.
2. Create a new module and then enter the following statement in the Declarations section:
   

         Option Explicit
   						

3. Create the following procedure:
   

         Function GetXofY (F As Form)
   
         '*******************************************************************
         ' FUNCTION: GetXofY()
         '
         ' PURPOSE:
         '   Gets "X of Y" from the record navigation buttons on a form
         '   where X represents the position of the record in the recordset
         '   relative to Y, the total number of records.
         '
         ' INPUT:
         '   F - The form object whose "X of Y" you want to retrieve.
         '
         ' USES:
         '   In the header or footer section of a form, you can create a text
         '   box and set its ControlSource property to the following
         '   expression:
         '
         '      =GetXofY([Forms]![Form Name])
         '
         ' NOTE:
         '   If the form is a continuous form, you must recalculate the form
         '   in the Current event for the expression to update as you move
         '   from record to record. For example
         '
         '      Sub Form_Current()
         '         Me.Recalc
         '      End Sub
         '
   
         '*******************************************************************
            Dim RS As Recordset
            On Error Resume Next
            Set RS = F.RecordSetClone
            RS.MoveLast
            RS.BookMark = F.BookMark
            If (Err <> 0) Then
               GetXofY = RS.RecordCount + 1 & " of " & RS.RecordCount + 1
            Else
               GetXofY = RS.AbsolutePosition + 1 & " of " & RS.RecordCount
            End If
         End Function
   						

How to Use the GetXofY() Function in a Form

The following example demonstrates how to display "X of Y" in the Orders form for the Orders form and the Order Details subform:

1. Open the sample database NWIND.MDB.
2. Open the Orders form in Design view, and add a text box to the detail section with the following property settings:
   

         ControlSource: =GetXofY([Forms]![Form Name])
         Left: 0.9 in
         Top: 2.75 in
   						

3. Add a second text box to the detail section with the following property settings:
   

         ControlSource: =GetXofY([Orders Subform].Form)
         Left: 0.85 in
         Top: 3.5 in
   						

4. View the Orders form in Form view and navigate among order and order detail records.