This week I developed a report that will print monthly statements for all member firms. The statement will include the usual: names, addresses, charges, payments, and so forth. A member's statement may range from 1 to many pages depending on their activity. Nothing unusual.

First challenge - print a remittance slip at the bottom of the last page for each firm. I tried using a group footer with "force new page after group" but it prints immediately below the end of the group, not at the bottom of the page - as the customer wanted. Not to mention that it looked unfinished.

I Google'd a bit and blended several ideas into a quite simple solution that doesn't use much code.

First of all, I moved all the "remittance controls" (e.g. total charges, total payments) from the group footer into the page footer (which was not being used). However, controls in the page footer can't aggregate data, so I copied those same controls back into the group footer, made the group footer Visible=No, and then changed the controls in the page footer to simply reference those hidden aggregators in the group footer. (Personal note: I usually make hidden controls Red so I can quickly tell them apart in design view.)

Second part. We only want the remittance to show on the last page for each firm, so we need a strategy to hide the page footer. Fortunately, the normal events fire for a group header or footer even if they're set for Height=0 or Visible=No. And here is all the code you need to accomplish that:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Me.PageFooterSection.Visible = True
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.PageFooterSection.Visible = False
End Sub

The way this works is that when Access open the report with the first group, the GroupHeader0_Format event fires, and we turn off the page footer. The page footer stays off until the group ends and the GroupFooter0_Format event fires, which turns the page footer back on & displays our remittance. The next group fires the Header event and we're "off" again.

Second challenge - print an overlay on the December statements to remind members about dues payments. My first attempt was to use a Picture, but Access applies it as a watermark (under the text) instead of on top of the text. Stay tuned.

EDIT December 16:
After testing the completed report, with user-selectable office code. it turns out that this approach did not work after all -- see my December 16 entry for the new version.