Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 3.5 Create a Page-Range Indicator on Each Page

3.5.1 Problem

You're creating a report that contains a large number of items. To make it easier to see the range of items on each page, you'd like to create a page-range indicator. This would show the first and last items on the page, as in a telephone book. Is there a way to do this?

3.5.2 Solution

The answer to your question is a qualified yes. You can create such a page-range indicator, but placing it anywhere but in the page footer is difficult. Although you can place it in the page header, the method to do so is quite complex and is the subject of a topic in the Microsoft Access Solutions database (SOLUTIONS.MDB), which shipped with Access 95 and Access 97. You can also download an Access 2000 version of this very useful sample database, called Solutions9.mdb. Search for that name at http://msdn.microsoft.com to find the download.

Because Access prints documents from top to bottom, by the time you know the last item on the page it's too late to print it at the top of the page. The Solutions database workaround involves forcing the report to format itself twice, capturing the page ranges for all the pages during the first pass and storing the values in an array. When it makes the second pass, you supply the values from the array. That solution requires VBA and is cumbersome. The solution we present here focuses on a simpler method, placing the information you need in the page footer. If you can live with that placement, this solution is straightforward.

To create a page-range indicator on your own reports, follow these steps:

Create a new report or open an existing one in design view. Make sure that the report includes page header and footer sections (if it doesn't, choose Format Page Header/Footer to add them). In the page header section, add a text box and set its properties, as shown in Table 3-8. This text box will hold the first row's value when you print the page.

Table 3-8. Property values for the hidden text box in the report's page header

Property

Value

Name

txtFirstItem

Visible

No

Add a text box in the report's page footer section. None of its properties are important to this technique except one, its ControlSource property. Set the text box's ControlSource property to be the expression:
```
=[txtFirstItem] & " -- " & [ProductName]
```
replacing the [Product Name] reference with the name of the field you'd like to track in the page-range indicator. This must match the field name you used in Step 1.
Set the OnFormat event property for the report's page header section to be the following event procedure:
```
Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtFirstItem = Me.ProductName
End Sub
```
This tells Access to run the code every time it formats the page header (once per page). Figure 3-10 shows the report and the properties sheet as they will look after you've assigned the property.

Figure 3-10. The sample report, rptPageRange, after setting the OnFormat event property

Save and run your report. You should see the page-range indicator as in the sample report, rptPageRange.

To view an example of this solution, load the rptPageRange report from 03-05.MDB in preview view (see Figure 3-11). You'll see, at the bottom of each page, a listing of the items printed on that page.

Figure 3-11. rptPageRange includes a page-range indicator in the page footer

3.5.3 Discussion

The technique presented in this solution is based on the fact that when Access prints the page header (or the report header or a group header), it gives you access to the row of data it's about to print. The same goes for footers, in reverse—there you have access to the row of data that's just been printed.

When you call the event procedure from the Format event of the page header, you place the data from the page's first row into the hidden text box, txtFirstItem. The data in that text box doesn't change until you again format the page. When Access gets to the bottom of the page and attempts to print the page footer, it calculates the value of the text box you've placed there. That text box retrieves the value you previously stored in txtFirstItem and combines it with the data from the last row that printed on the page to create the page-range indicator.

Though simple, this method does have a few limitations:

The page-range indicator must go in the page footer. If you attempt to place it in the page header, the data it prints will always be off by a page in one direction or the other, depending on how you're viewing the report.
For this method to work, you must include the page header section on every page. (The PageHeader property for the report must be set to All Pages.) Because you must fill in the hidden text box once for each page, the only place you can do that is in the page header.

It's interesting to note that within an expression you place within the Properties window, you must surround field names and control references with brackets ([ ]). Within VBA code, the brackets are optional, and you generally don't need to use them unless the field or control name isn't a valid VBA identifier (if it includes spaces in its name, for example).

[ Team LiB ]