ACC: Sample Function to Determine Current Page of a Form (121669)

SUMMARY

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():

Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x and 2.0).
Open the Employees(page break) form in Design view (or Employees form in Microsoft Access 7.0 or earlier).
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
Set the Address control's OnDblClick property to:
```
=WhichPage(Form,[Address])
						
```
Save and then close the form.
Create a module and type the following lines in the Declarations section if they are not already there:
```
Option Compare Database
Option Explicit
					
```

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

Close the module and save it as Module Test.
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.