ACC: Sample Function to Determine Current Page of a Form

ID: Q121669

The information in this article applies to:

SUMMARY

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

This article shows you how to create a sample, user-defined function that you can use on a multiple-page form that contains page breaks to determine which page of the form is current.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

The following steps demonstrate how to create and use the sample function WhichPage():

1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 

   1.x and 2.0).
2. Open the Employees(page break) form in Design view (or Employees form in 
   Microsoft Access 7.0 or earlier).
3. Set the EmployeeID control's OnDblClick property to: 

      =WhichPage(Form,[EmployeeID])

   NOTE: In Microsoft Access 1.x and 2.0, there is a space in the Employee
   ID field name.

   NOTE: In Microsoft Access 97 and 7.0, set the Enabled property to
   Yes and the Locked property to No for the EmployeeID field
4. Set the Address control's OnDblClick property to: 

      =WhichPage(Form,[Address])

   Save and then close the form.
5. Create a module and type the following lines in the Declarations section 
   if they are not already there:

      Option Compare Database
      Option Explicit
6. Type the following procedure: 

      Function WhichPage (F As Form, MyControl As Control)
      Dim Page, I As Integer
      Dim C As Control
      Page = 1

      For I = 1 To F.Count - 1
        Set C = F(I)
        If TypeOf C Is PageBreak Then
           If C.Top < MyControl.Top Then Page = Page + 1
      End If
      Next I

      MsgBox "Page= " & Page
      End Function
7. Close the module and save it as Module Test.

8. Open the Employees(page break) form in Form view. Double-click the 

   EmployeeID field, and then the Address field. Note that the function
   displays the number of the page containing the control.
Additional query words: 
Keywords          : kbusage FmsHowto 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 21, 1998