navigation in preview via VB

  • Thread starter Thread starter Robert Langen
  • Start date Start date
R

Robert Langen

In Access 2000 in a report I need to calculate various things via VB code.
The calculations are done within the event OnPrint of the group's header
section.

It works fine as long as the report is printed to a printer. But if the
report is previewed, the user e.g. could skip from page one to the last
page. In this case calculations are performed for the first and the last
page, but not for the pages between, which results in wrong sums.

So I´m looking for a method in the reports preview mode to skip page by page
until the last page is reached. All OnPrint events would be executed in the
right order. It could work like the small navigation buttons at the bottom
of the preview, but I don´t know how to use the same functionality in VB.

Has anybody an idea?
 
Perhaps a different approach, say, calculating in the query rather than in
the Report events would be a good idea?

Not only can pages be skipped, but Report events may fire more than once and
may fire many, many times if someone pages back and forth in Preview. That
means calculations that are cumulative, as it would seem yours are, have to
be done with EXTREME care.

If you'd care to clarify the nature of the calculations, perhaps someone
could give you a more specific answer.

Larry Linson
Microsoft Access MVP
 
Some of the calculations could be done in the query, others would be really
difficult.

I thought that printing all pages in preview (like the user clicks on the
next page control) would result in executing all OnPrint events. A module
variable then contains the state that all calculations are finished, so
further calculations would abort if the variable is true. This would avoid
wrong cumulative calculations.

Robert
 
Robert:

The problem with your approach, is that it assumes a sequential running
through the available pages in the report to do proper calculations (say
overall totals.) This of course you can not count on to occur. To work
around this you'd be better off creating subsidiary queries which do the
totals. If you design your subsidary query to incorporate a subquery (e.g.
where customerID (or what ever primary key you are using,) is < the current
group PK, then you should always get the proper values no matter where the
user pages to in the report or even if they go backwards in the report.
With this approach, you'd take your report query and use it as the basis for
the sub query and then in the group header or footer, simply use a SQL
statement to open a recordset to get a value and then place that value in an
unbound control on the report. There's obviously overhead to this
approach, but it will provide the proper values.
 
Back
Top