When you create a report that contains a large number of items, you can include a page-range indicator to display the first and last items on the page, similar to a telephone book. All you need is some hidden text in the page header of the report and a little bit of VBA code.
Download the sample database
After you create a page-range indicator, figuring out how to place it where you want it on the page is not a trivial problem. Although you can place the page-range indicator in the page header, doing so is quite complex and is the subject of a topic in the Microsoft Access Solutions database for Access 2000 (see the Solutions9 link at the end of this article).
Because Access prints reports from top to bottom, by the time that you can determine where the end of the page will be, it is too late to print the page-range indicator at the top of the page. The workaround in the Solutions9 database forces the report to format itself twice. On the first pass, it captures the page range associated with each page and stores those values in an array. On the second pass, you supply the values from the array, and those values are displayed in the page header. That solution requires a good deal of VBA code, and is quite cumbersome.
In contrast, this article provides a simple way to place the page-range indicator in the footer.
Adding a page-range indicator
To experiment with a page-range indicator on a report, use the reports in the sample database, CreatePageRangeIndicator.accdb.
To add 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 header and footer sections on each page. (If it doesn't, right-click the report's design surface, and then select Page Header/Footer in the shortcut menu.)
In the page header, add a text box; set its name to txtFirstItem and its Visible property to No.
In the page footer, add a text box; set its ControlSource property to the following expression, substituting a field name other than [Product Name]. that is pertinent to your own report. The first item in the expression must match the name of the text box you created in Step 1.
=[txtFirstItem] & " -- " & [Product Name]
Add a Format event handler for the Page Header section of the report. In particular, set the OnFormat property for the section to [Event Procedure], and then add the following code to the event handler. Again, change the [Product Name] field to match your own data, and make sure txtFirstName matches the name of the text box in the header of your report.
Private Sub PageHeader0_Format( _
Cancel As Integer, FormatCount As Integer)
txtFirstItem = [Product Name]
This tells Access to run the code every time it formats the page header (one per page). Figure 1 shows the sample report, rptPageRange, in CreatePageRangeIndicator.accdb, with all the properties set appropriately.
Figure 1. The sample report, rptPageRange, after setting the OnFormat event property
Save and run your report in Print Preview mode. You should see the page-range indicator as in the sample report, rptPageRange (see Figure 2).
Figure 2. The sample report, rptPageRange, displays a page-range indicator in the page footer
The solution in this article uses the way that Access prints the header for a page, report, or group. Specifically, it gives you access to the row of data that it is about to print. Footers are similar, but there, you have access to the row of data that has just been printed.
When Access calls the event procedure from the Format event of the page header, you place the data from the first row of the page into the hidden text box, txtFirstItem. The data in that text box does not change until you format the page again. When Access gets to the bottom of the page and attempts to print the page footer, it calculates the value of the text box that you placed there. That text box retrieves the value that you previously stored in txtFirstItem, and combines that value with the data from the last row that printed on the page, which creates the page-range indicator.
The technique does have a few limitations:
You must place the page-range indicator in the page footer. If you try to place it in the page header, the data that Access prints will always be off by a page in one direction or another, depending on how you view the report.
You must include a page header section on every page. (That is, 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 page header is the only place that you can do that.
You must surround field names and control references with brackets () in any expression that you place in the Properties window. The brackets are optional in VBA code, and you generally do not need to use them unless the field or control name is not a valid VBA identifier (for example, if it includes spaces in the name).
Watch the Video
Video Length: 00:05:17
File Size: 5.21 MB WMV