I discovered that the OnFormat event runs twice for all sections of a report if a [Page] of [Pages] control is used to display page number and total pages of the report. I find this very disturbing and it's a disaster for some particular calculations in my report footer, not to mention the extra time it takes to format a very cumbersome report.
I need to display the total pages of a report so I need a way around the double event runs. Do I have to figure my own total pages based on the number of records I have in the report?
Running through the events twice is typical of a MSAccess report. A simple work-around for this would be to force Access to skip past the event once it has fired once. To do this, you can use a Static Variable. For Example, in the OnFormat event your event code could start like this:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static x As Integer
If x > 0 Then Exit Sub Else x = x + 1
'....Your code here....
Couldn't you just use the FormatCount value in code? The Format event starts FormatCount at 1. If it fires the Format event on the same section (Detail, for example in Lynx's post) it will increase FormatCount by 1. This way you could do:
If FormatCount = 1 Then
I use this when I'm summing values across an entire section/report for calculated fields. If someone notices some fallicy in this, let me know. I'm nowhere near an expert in Access.
These solutions are fine to prevent the detail code to run twice in a row for the same record. My problem is that the code runs once for each detail record, then it runs for the page and report footer and starts over with the detail code for each record again. When I remove the "Page of Pages" this does not happen. The report obviously must go through all records to find out how many pages are in the report before it can display the "Page of Pages" part on each page. What bothered me was that the detal code runs again.
I have tried to use the OnRetreat event but that event is never triggered. I have tried to set variables for each record, that once set, prevents the code from running again for that record. The easy solution right now is to not use the "Page of Pages" field.
if you have a lengthy complex report then I wouldn't advocate using page n of pages as it forces access to do the report twice. ON a very small or smple report it works well, but if you have any concerns that a report takes a long time to run, rip out the page n of pages it will approximately halve the report generation time. Unless your user base explicitly demand it don't do it.
Just curious, but where exactly do you have the ="Page " & [Page] & " of " & [Pages] placed on your Report???? I have mine in the PageFooter and run about 751 pages without a hiccup. I have never had a problem with that part of the Report. Just curious.
My ="Page " & [Page] & " of " & [Pages] code is in the PageFooter. It has worked fine for the last 9 years for me and it still does 99% of the time. It does make all report code run twice (upsetting), but I never cared, never actually knew, it never caused a problem and I had no need to dwell in it. Now for the first time I have a report where I cannot use calculation fields - like Sum([DetailData]) - in the ReportFooter and expect it to calculate right, because some fields in the detail section has no data source, they are dynamic and receive data in the code on the OnFormat event. With dynamic fields in the calculation I have to calculate report totals on the run in code and the double runs caused problems.
I think the case is now closed because I have found a solution: Don't use ="Page " & [Page] & " of " & [Pages]. No double runs. No problem.