Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

I had a customer with an Access database who wanted me to tack a page with completely different info and format to the last page of a main report.

This main report would run in an automated fashion and is emailed to a list of users and it is also able to be run manually and pulled up in the app.

The main report had a page header that I needed to not appear on the last page, and the new page had a different header I wanted to appear on that page.

I did it, but it was an interesting challenge and will really get confused if you start jumping around to different pages in the report, but is fine if you just page through it sequentially.

What I ended up doing was to add a subreport in the main Report “Report Footer” at the very bottom. I already had the section running and it has a totals section that I wanted to appear at the end of the last page of the main report, but then start a new page.

I added a Page Break control right after the report subtotals, then the subreport control right after that.

Then I updated the Report > Report Footer Print event to hide the page header.

This caused the page header to start being hidden on the next page, why? Because the page header had already been printed on the page the Report Footer first appears on, but then the page break starts a new page and now the page header’s visible property is set to false and so it won’t appear on the next page.

Next I wanted that page to disappear if there was no data to display, and to show a label right under the report totals saying there was no data for the final page. This was pretty simple. In the Report Footer format event I checked the subreport “HasData” property. If there is no data in the subreport this is set to false, and so I check that condition and if the subreport has data I make sure the page break is displayed, the subreport control is displayed, and the No Data label is hidden, otherwise I hide the page break, the subreport, and show the No Data label.

This worked well. ideally though, I should set the page header to display again if you go backwards in the report. When I jumped to the end of the report by putting in the last page, it turned off the page header, and then as I went backwards through the pages, the page header was still hidden when it rendered those pages. I’m not going to worry about this for now, as I don’t expect the user to do that. If they complain about it, I’ll have to figure out where to add the code to make sure when paging backwards the page header will appear as expected. This can only happen when they manually run the report and only in a certain sequence of navigating.

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.