Report On Format event executing twice

  • Thread starter Thread starter Jim Burke in Novi
  • Start date Start date
J

Jim Burke in Novi

I have some code that I execute in a report detail section On Format event. I
keep some running totals on various things in that code. For some reason, the
event seems to get triggered twice for each row that is processed. Is there a
reason for this? I've tried using On Print rather than On Format, and every
other thing I could think of, but my numbers end up being twice what they
should be. Well, almost twice! Depending on the data, sometimes dividing by 2
at the end gives the right value, but sometimes it's off by a little bit. Any
ideas? Any help is appreciated. Thanks.
 
Attempting to accumulate a value across several pages by using the section
events is unreliable in Access. For example, if you preview a report, and
jump to page 5 (by typing 5 into the navigation buttons at the bottom of the
page) without previewing each page, the events for the intervening pages are
not fired reliably, and so the accumulated total will be wrong. Similarly if
you print just page 5 of the report (via the Print dialog), the events for
the skipped pages don't fire reliably. You need to find another way.

You can work with the section events if you only need to accumulate a value
within a page (e.g. to show in the Page Footer.) But the Format event can
fire multiple times for a record as you found. One thing you can do is test
FormatCount to see if this is the first time it's fired for the record.

You also need to be aware that the report can fire the Retreat event,
indicating that it is now backtracking on records where Format fired and
those record(s) will not be on the page.

Examples of situations that can affect this behavior:
- properties of the sections (such as Keep Together, Force New Page)
- properties of the groupings (such as With First Detail),
- runtime properties of the report (such as NextRecord)

But all this is a bit academic anyway, given that the whole idea of trying
to accumulate values programmatically is flawed, as shown in the first
paragraph above. The solution will be to find a different approach. You may
be able to find a creative way to use the Running Sum property to achieve
what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
If you look at the declaration for the Format and Print events, you should
notice they're

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

and

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

In the case of the Format event, FormatCount is an Integer value that
specifies whether the Format event has occurred more than once for a
section. For example, if a section doesn't fit on one page and part of it
moves to the next page of the report, Microsoft Access sets the FormatCount
argument to 2. Similarly, PrintCount is an Integer value that specifies
whether the Print event has occurred more than once for a record. For
example, if part of a record is printed on one page and the rest is printed
on the next page, the Print event occurs twice, and Microsoft Access sets
the PrintCount argument to 2.

You should only run your code when the value is 1.
 
Looks like that did it. Exiting if the formatcount <> 1 seems to work. Thanks.
 
Jim said:
Looks like that did it. Exiting if the formatcount <> 1 seems to work. Thanks.
Click to expand...


The key word in "formatcount <> 1 seems to work" is the
word "seems". As Allen tried to point out, FormatCount is
not reliable in quite a few common situations.

One obvious situation is with a group's KeepTogether
property set. In the case where the entire group (or just
the header and first detail) do not fit on the current page,
the FormatCount can be 1 two or more times for a section in
the group. A general rule that I use to think about
FormatCount is that it is set to 2 when the section is split
across two pages, but not when a retreat is includes more
than a single section. (PrintCount is not much better.)

Regardless of what you think "seems" to work today, it is
almost a certainty that it won't work after you make some
seemingly simple change to the report.

You really need to use a running sum text box instead of
fooling around with an unreliable code approach.
 
I have the multiple event firing problem too. However, the FormatCount is
always 1. Something changed in my code, because the group events worked prior
to this.

Can you provide examples of how to capture group totals without using group
events?

Thanks,
Keith
 
Back
Top