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

• Microsoft Access version 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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.

MORE INFORMATION

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: