Access Cookbook - Ken Getz [67]
The two events described in this solution are the Format event and the Print event. Normally, you'll attach a VBA procedure to the Format event of a section if you want to affect the section's layout on the page. You'll use the Print event to make calculations based on the data as you know it's going to print. When Access calls your macro or VBA code from the Print event, you are guaranteed that the current row is going to be printed. You can't assume this from the Format event, because Access calls the code attached to the Format event before it decides whether or not the current row will fit on the current page. From either event, you have access to the current row of data that's about to be printed, and you can use that as part of your event procedure.
In this case, calculating a page total requires two steps: you must reset the page total for each page (and before you start printing the report), and you must accumulate the value in each row as you print the row.
The accumulation part is simple: every time you print a row, the procedure attached to the detail section's Print event adds the value in the current row's Freight field (or whatever field you're tracking on your own report) to the current value in txtPageTotal. When Access needs to print the page footer, that value is filled in and ready to print. The event procedure should be written on the Print event, not the Format event, to ensure that you never add a value to the page footer unless you're sure the row will be printed on the current page. Calling the code from the Print event guarantees this.
You can reset the page total so it starts from zero from the Format event of the page header section. Because this is the first section that will print on every page, resetting the total in the page header should work. You could use the Print event here, but because you're guaranteed that the page header section will fit on its page, you might as well do the work as early as possible. The problem here arises from the fact that, in some reports, you may tell Access to print the page header only on pages where there isn't a report header (see the report's PageHeader property). If you do this, Access won't format the page header on the first page, and it therefore won't call the necessary code. To make up for this, the example report (rptPageTotals in 03-07.MDB) also calls the code from the report header's Format event. Because this event occurs only when Access prints the first page, there's no redundancy here. You may not need to reset the page total from the report header, but it can't hurt.
Be wary of performing any calculations during a section's Format event. Because you aren't guaranteed that the section will actually print on the current page, you could be calculating based on a value that won't be a part of the page. Making this mistake in the sample report, for example, would be a major error. Because this report is set up so that Access will print a group only if the entire group can fit on a page, it might format a number of rows, then decide that the whole group can't fit. Each time it attempts to format a row, it will call the code attached to the Format event, which will add the value to the total. To avoid this problem, perform calculations from a section's Print event only. Use the Format event to change the layout of a section—for example, to make a specific control visible or invisible, depending on the data you find in the current row (see the Solution in Recipe 3.4 for an example of this usage).
3.8. Avoid Unwanted Blank Rows on Mailing Labels
Problem
When you print mailing labels, especially when you use a small font size and place the address text boxes close together, you sometimes get unwanted blank rows in the addresses when the labels print. You also can't seem to use lines or graphics on your labels without causing blank rows. How can you get your