Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. 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.

2. 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.

3. 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

4. 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 reversethere 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).