Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    81

    Unanswered: OnFormat runs twice in report

    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?

    Anyone else bothered about this?

    Bjorn

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    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:

    Code:
    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....
       '............................
    End Sub

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    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
    'Code
    EndIf

    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.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Jan 2003
    Posts
    81
    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.

    Bjorn

  5. #5
    Join Date
    Jul 2004
    Posts
    156
    What I use is "PrintCount" is the Print event. Have you tried that? It seems to not double-back because that's what Format does. I'll do some more research in my Access books.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Bjorn,

    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.

    have a nice one,
    BUD

  8. #8
    Join Date
    Jan 2003
    Posts
    81
    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.

    Thanks DocX for the PrintCount tip!

    Thanks for your help,

    Bjorn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •