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

Recipe 3.7 Create a Page Total

3.7.1 Problem

Access allows
you to create a group total in the group footer on a report or a
report total on the report footer, but you can''t
find a way to create a page total in the page footer. You understand
that this problem doesn''t come up too often, but for
your report you could really use this element. Is there a way to sum
up values over a single page?

3.7.2 Solution

It''s true that Access
allows aggregate calculations only in group or report footers. You
can, however, easily create page totals using two simple macros. This
solution demonstrates this technique and shows how to add this
capability to any of your own reports.

To create page totals for your own reports, follow these steps:

1. 
   Create your report, and sort and group
   the data as desired. In the report''s page footer
   section, include a text box named txtPageTotal.

   
   

2. 
   Create the following event procedure in
   the Format event of the page header and report header sections:

   
   

   Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
   Me.txtPageTotal = 0
   End Sub
   Private Sub ReportHeader0_Format(Cancel As Integer, FormatCount As Integer)
   Me.txtPageTotal = 0
   End Sub

3. 
   Create an additional event procedure
   in the OnPrint event for the detail section:

   
   

   Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
   Me.txtPageTotal = Me.txtPageTotal + Me.Freight
   End Sub

4. 
   Save your report. When you run it, you will see the total of the
   field you set in the OnPrint event procedure.

   
   

Now load rptPageTotals from

03-07.MDB in preview
view (see Figure 3-14). This report is used to track
orders and their freight costs. The items are grouped by month, and
each group has a total in the group footer. At the bottom of each
page, you''ll see the total for all items on the
current page.

Figure 3-15 shows the sample report in design view.

Figure 3-14. Page 2 of the rptPageTotals report with page totals

Figure 3-15. rptPageTotals in design view

3.7.3 Discussion

Access makes it simple to sum values in
group or report footers: use the

Sum function in
the ControlSource property for a text box. For example, to sum the
freight costs in either a group footer or a report footer, you could
use an expression like this:

=Sum([Freight])

and Access would perform the sum over the range included in the
footer section (for either the group or the entire report). To create
a page total, however, you must dig a bit deeper into the way Access
prints reports.

The report-printing engine in Access
works as a forward-marching machine: the engine formats and then
prints each section in turn, such that each section is handled in the
order in which it appears on the page. The report-printing engine
deals first with the report header, then any page header, then any
group header, then each row of the detail section, and so on. At each
point, Access allows you to "hook"
into various events, doing work alongside its work.

The two events described in this solution
are the Format event and the Print event. Normally,
you''ll attach a VBA procedure to the Format event of
a section if you want to affect the section''s layout
on the page. You''ll use the Print event to make
calculations based on the data as you know it''s
going to print. When Access calls your macro or VBA code from the
Print event, you are guaranteed that the current row is going to be
printed. You can''t assume this from the Format
event, because Access calls the code attached to the Format event
before it decides whether or not the current row will fit on the
current page. From either event, you have access to the current row
of data that''s about to be printed, and you can use
that as part of your event procedure.

In this case, calculating a page total requires two steps: you must
reset the page total for each page (and before you start printing the
report), and you must accumulate the value in each row as you print
the row.

The accumulation part is simple: every
time you print a row, the procedure attached to the detail
section''s Print event adds the value in the current
row''s Freight field (or whatever field
you''re tracking on your own report) to the current
value in txtPageTotal. When Access needs to print the page footer,
that value is filled in and ready to print. The event procedure
should be written on the Print event, not the Format event, to ensure
that you never add a value to the page footer unless
you''re sure the row will be printed on the current
page. Calling the code from the Print event guarantees this.

You can reset the page total so it
starts from zero from the Format event of the page header section.
Because this is the first section that will print on every page,
resetting the total in the page header should work. You

could use the Print event here, but because
you''re guaranteed that the page header section will
fit on its page, you might as well do the work as early as possible.
The problem here arises from the fact that, in some reports, you may
tell Access to print the page header only on pages where there
isn''t a report header (see the
report''s PageHeader property). If you do this,
Access won''t format the page header on the first
page, and it therefore won''t call the necessary
code. To make up for this, the example report (rptPageTotals in

03-07.MDB ) also calls the code from the report
header''s Format event. Because this event occurs
only when Access prints the first page, there''s no
redundancy here. You may not need to reset the page total from the
report header, but it can''t hurt.

Be wary of performing any calculations during a
section''s Format event. Because you
aren''t guaranteed that the section will actually
print on the current page, you could be calculating based on a value
that won''t be a part of the page. Making this
mistake in the sample report, for example, would be a major error.
Because this report is set up so that Access will print a group only
if the entire group can fit on a page, it might format a number of
rows, then decide that the whole group can''t fit.
Each time it attempts to format a row, it will call the code attached
to the Format event, which will add the value to the total. To avoid
this problem, perform calculations from a section''s
Print event only. Use the Format event to change the layout of a
sectionfor example, to make a specific control visible or
invisible, depending on the data you find in the current row (see the
Solution in Recipe 3.4 for an example of
this usage).