Microsoft KB Archive/128883

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

Article ID: 128883

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q128883



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



SUMMARY
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
 Open the sample database NWIND.MDB.  Create a new module and then enter the following statement in the Declarations section:

     Option Explicit   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:

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

<pre class="fixed_text">     ControlSource: =GetXofY([Forms]![Form Name]) Left: 0.9 in     Top: 2.75 in                        </li>  Add a second text box to the detail section with the following property settings:

<pre class="fixed_text">     ControlSource: =GetXofY([Orders Subform].Form) Left: 0.85 in     Top: 3.5 in                        </li> View the Orders form in Form view and navigate among order and order detail records.</li></ol>

Keywords: kbusage KB128883

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.