Create page totals in report HEADER

[Guest]

I used MS-Knowledge base Article 216311 to show a page-total in the page footer of each page of a report and it works fine , but I also want to have the same page-total appear in the page header. I've tried variations of the code, but always get either "0" or the first-page total on the second page, etc. because of the sequence in which it calculates. Same with having the control in the header refer to the control in the footer. Any ideas?

 

[fredg]

I used MS-Knowledge base Article 216311 to show a page-total in the
page footer of each page of a report and it works fine , but I also
want to have the same page-total appear in the page header. I've
tried variations of the code, but always get either "0" or the
first-page total on the second page, etc. because of the sequence
in which it calculates. Same with having the control in the header
refer to the control in the footer. Any ideas?

Off the top of my head, I think you could do it like this.

1) Add a table to your database with 2 Fields
[PageID] AutoNumber Indexed No Duplicates
and
[TotalAmt] Currency datatype (or whatever datatype matches the report
control).
Name the table "tblTrackAmt"

Add as many blank records as you think the maximum number of pages in
the report will be (more records is OK).

2) If you don't already have one, add a control to the report that
computes [Pages] ,i.e. =[page] & " of " & [Pages] . You can make it
not visible if you wish.

3) Then add some code to the Page Footer Print event:

CurrentDb.Execute "Update tblTrackAmt Set tblTrackAmt.TotalAmt = " &
Me![PageFooterControlName] & " Where [PageID] = " & Me.[Page] & ";",
dbFailOnError

4) Code the Page Header Format event:
[HeaderControlNamel] = DLookUp("[TotalAmt]","tblTrackAmt","[PageID] =
" & Me![Page]

The [Pages] control forces the report to be formatted and run twice.
The first time through it computes the page total and sends it to the
table. The second time (when it's actually shown in preview) it fills
in the Header with the correct page total.

 

[Guest]

Thanks, Fred ... a clever little trick! However, no matter what I do, I get a "Type Mismatch" error on the last line of code. Any suggestions??

----- fredg wrote: ----

I used MS-Knowledge base Article 216311 to show a page-total in th
page footer of each page of a report and it works fine , but I als
want to have the same page-total appear in the page header. I'v
tried variations of the code, but always get either "0" or th
first-page total on the second page, etc. because of the sequenc
in which it calculates. Same with having the control in the heade
refer to the control in the footer. Any ideas

Off the top of my head, I think you could do it like this

1) Add a table to your database with 2 Field
[PageID] AutoNumber Indexed No Duplicates
and
[TotalAmt] Currency datatype (or whatever datatype matches the repor
control)
Name the table "tblTrackAmt

Add as many blank records as you think the maximum number of pages i
the report will be (more records is OK)

2) If you don't already have one, add a control to the report tha
computes [Pages] ,i.e. =[page] & " of " & [Pages] . You can make i
not visible if you wish

3) Then add some code to the Page Footer Print event

CurrentDb.Execute "Update tblTrackAmt Set tblTrackAmt.TotalAmt = "
Me![PageFooterControlName] & " Where [PageID] = " & Me.[Page] & ";"
dbFailOnErro

4) Code the Page Header Format event
[HeaderControlNamel] = DLookUp("[TotalAmt]","tblTrackAmt","[PageID]
" & Me![Page

The [Pages] control forces the report to be formatted and run twice
The first time through it computes the page total and sends it to th
table. The second time (when it's actually shown in preview) it fill
in the Header with the correct page total