Need Help with Code

[Guest]

I need to create a summary report of another report, broken down by page,
with a currency total for each page. Since this seems to be impossible, I'm
hoping I can approach it from a different angle.

There will always be five records on a page, except for possibly the last
page. Is there a way to break down records in a query in groups of five
sequentially, number each group (to simulate page numbers), then total a
currency field for each group?

Excessive, profuse thanks to anyone that can help me with this.

 

[Allen Browne]

Hi Anne

Can't think of a really simple solution, but here's a couple of
possibilities.

1. Temp table
==========
Write the values for the report to a temp table that has an AutoNumber
field. Before you start, delete any records in the temp table, and use ADOX
to set the Seed of the Column to 0. Then use a Totals query to GROUP BY (ID
\ 5) and Sum(Amount).

2. Report events
=============
It is possible to use the events to calculate the total for a page, as
described in this article:
Reports: Page Totals
at:
http://members.iinet.net.au/~allenbrowne/ser-11.html

You could then use the Format event of the Page Footer section to write the
page number and calculated to to a table:
strSql = "INSERT INTO tblPageTotal ( PageNum, PageTotal ) " & _
"SELECT " & Me.Page & AS PageNum, " & curTotal & " AS PageTotal;"
dbEngine(0)(0).Execute strSql

The other report would then SELECT DISTINCT on this table. It will only show
the totals for the pages you previewed or printed, but it may get you out of
trouble.