Hi Leslie,
I tried your example and it came up with the same error I was getting
earlier. I did however find out the way this needs to be done. Through vba
and here's the information. Again, I appreciate your help/time.
Link:
http://office.microsoft.com/en-us/access/HA011224441033.aspx?pid=CH063650511033
Placing sums in the page header or footer
Sometimes you want to calculate a total for each page of a report. The page
footer and header sections, however, do not support calculated controls that
use aggregate functions such as Sum. You can solve this problem with a little
understanding of how reports work in Access, and a small amount of Microsoft
Visual Basic® for Applications (VBA) code. VBA is the programming language
that Access uses.
Access divides a report into sections and processes each section in turn
according to its type. The Report Header section, for example, is printed
once, at the beginning of the report. The Page Header section, on the other
hand, is printed at the top of every single page. Because reports often have
many pages, the Page Header section tends to be called often. Normally, the
most often called section is the Detail section because it's called once for
every row in the report's record source.
So, when you run a report, the Access report engine gets busy processing the
report sections, many of them repeatedly. While it's processing report
sections, Access also triggers a series of events (event: An action
recognized by an object, such as a mouse click or key press, for which you
can define a response. An event can be caused by a user action or a Visual
Basic statement, or it can be triggered by the system.) that can be responded
to by VBA code. For example, the section's Format event occurs when Access
has selected the data to go in a section, but before the data is actually
formatted or printed. You can use the Format event to intervene when you want
to change a section layout on a page.
After the code in the Format event has been run, Access then formats the
data and readies it for printing. At this point, Access triggers the
section's Print event. The Print event is a good place to perform
calculations that are based on data that will appear on the current page
because you're guaranteed the data will actually be printed on that page.
There's no such guarantee with the Format event because Access can determine
that the section won't fit on the current page.
To calculate and print page totals, you can use the Print event for the
Detail section and for the Page Header section, in combination with two text
box controls that you insert in the Page Footer section. This technique
consists of the following steps:
You create a text box in the Page Footer section to hold the page total for
the dollar sum of sales, and another text box in the Page Footer section to
hold the page total for the quantity of products sold. Let's call these
values txtPageSum and txtPageQuantity.
You place some VBA code in the Print event for the Page Header section that
will reset the value for each of these text boxes to zero. In this way, you
initialize the page totals at the top of each page.
You place some VBA code in the Print event for the Detail section to add the
values for the current record to the page sum and page quantity. This is
where you accumulate the values for the current page.
Here's the code for the Print event for the Detail section (called
Detail_Print) and the Print event for the Page Header section (called
PageHeaderSection_Print):
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
txtPageSum = txtPageSum + ExtendedPrice
txtPageQuantity = txtPageQuantity + Quantity
End If
End Sub
Private Sub PageHeaderSection_Print(Cancel As Integer, _
PrintCount As Integer)
txtPageSum = 0
txtPageQuantity = 0
End Sub
The PageHeaderSection_Print routine is called by the Print event of the page
header at the top of every printed page. Its job is to reset the two page
totals, txtPageSum and txtPageQuantity, to zero so that every page begins
anew with fresh, initialized page totals. The Detail_Print routine is called
by the Print event of the Detail section for each detail record. Its job is
to add the values for that record to the page total. So, the value of
ExtendedPrice is added to the value of txtPageSum to accumulate the page
total for the sales amount, and the value of Quantity is added to the value
of txtPageQuantity to accumulate the page total for quantity.
Note that the value of the PrintCount property is checked, before any
accumulating begins, to determine if it's set to one. Access increments this
property by one whenever the data for the current section is printed. Because
there are times when the Print event for the Detail section for a particular
record might be called more than once, checking the PrintCount value ensures
that you don't add the same value twice to a page total.
Leslie Isaacs said:
I'm no expert in access (I usually ask questions here, not answer them!),
but I think I've had this problem and I got round it by creating a textbox
in the page footer and setting its data source to the value of the textbox
in the report footer that contains the summary value you want. So if the
textbox in the report footer is called [txtTotal] then create a textbox in
the page footer and set its data source to '=[txtTotal]'.
No doubt there is a better way, but I think my suggestion works.
Les
Kelly said:
Hi, I have some simple calculations being displayed on a report using
Sorting
and Grouping. This is working fine.
I also have on the Report Footer giving me the complete totals, it gives
me
what I'm looking for but only on the last page. How can I get it to do
this
on every page of the report?
Thank You!
