Duplicate processing in Group Format Event

  • Thread starter Thread starter Robert Wetherill via AccessMonster.com
  • Start date Start date
R

Robert Wetherill via AccessMonster.com

I need some help within the GroupFooterX_Format event. I read another thread
but still don't understand why the Format event processes twice. I am tyring
to add up totals using VBA code and my variables get all screwed up because
the "GroupFooter1_Format" event processes twice. Can anyone explain why this
happens and how to get around it?

Thanks
 
The Format event fires when Access tries to place the section on the page.
Particularly where sections CanGrow or CanShrink and where the section must
KeepTogether, Access may fire the section's Format event, and discover it
does not fit on the page. At this point, it finishes that page and then
begins plotting how to fit the event on the next page (i.e. the Format event
then fires again on the next page.)

There is a Retreat event to indicate when Access is backtracking, but the
approach you are suggesting is still not going to work. You will get a
better result by using the Print event, but that still is not reliable. The
Print event can also fire multiple times, e.g. if there is code setting the
report's NextRecord property.

Even worse, the events might not fire at all. If you preview a report, and
jump to page 5, Access might not fire the Print event for all the records on
pages 2 - 4 of the report. Therefore if you try to accumulate a total across
more than one page, the approach is doomed to fail.

An alternative is to use a RunningSum text box on the report. You could
collect a total using a text box with these properties:
Control Source: =[Quantity] * [UnitPrice]
Running Sum: Over All
Format: Currency
Visible: No
Name: txtAmount
Then in the Report Footer section, you can display the accumulated total in
another text box with Control Source:
=[txtAmount]

For that simple example, you could have just used:
=Sum([Quantity] * [UnitPrice])
directly in the final text box. It simply illustrates a technique that you
can use to accumulate values reliably across a group or the entire report.
 
Allen, Thanks for the answer! This explains alot and I can now try to replace
the hair that I lost!

Allen said:
The Format event fires when Access tries to place the section on the page.
Particularly where sections CanGrow or CanShrink and where the section must
KeepTogether, Access may fire the section's Format event, and discover it
does not fit on the page. At this point, it finishes that page and then
begins plotting how to fit the event on the next page (i.e. the Format event
then fires again on the next page.)

There is a Retreat event to indicate when Access is backtracking, but the
approach you are suggesting is still not going to work. You will get a
better result by using the Print event, but that still is not reliable. The
Print event can also fire multiple times, e.g. if there is code setting the
report's NextRecord property.

Even worse, the events might not fire at all. If you preview a report, and
jump to page 5, Access might not fire the Print event for all the records on
pages 2 - 4 of the report. Therefore if you try to accumulate a total across
more than one page, the approach is doomed to fail.

An alternative is to use a RunningSum text box on the report. You could
collect a total using a text box with these properties:
Control Source: =[Quantity] * [UnitPrice]
Running Sum: Over All
Format: Currency
Visible: No
Name: txtAmount
Then in the Report Footer section, you can display the accumulated total in
another text box with Control Source:
=[txtAmount]

For that simple example, you could have just used:
=Sum([Quantity] * [UnitPrice])
directly in the final text box. It simply illustrates a technique that you
can use to accumulate values reliably across a group or the entire report.
I need some help within the GroupFooterX_Format event. I read another
thread
Click to expand...
[quoted text clipped - 7 lines]
Thanks
Click to expand...
Click to expand...
 
Back
Top