Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, December 2004. Updated May 2006.

Print a page with 3 evenly spaced mailing slips

The Label Wizard matches most mailing labels, but what if you need to divide a page into three slips with address panels in exactly the same place? Most printers require a top and bottom margin, so just dividing the remaining space by three does not place the address panels correctly.

The solution is to add a Group Footer section to act as a spacer between the slips. Set the height of this spacer to the top margin plus the bottom margin. If you can then suppress this footer underneath the third slip, you end up with a page that prints like this:

Steps to create the report

1. Create a report based on the table or query that contains the data.
2. In report design view, if you see Page Header and Page Footer sections, remove them by clicking Page Header/Footer on the View menu in Access 95 - 2003. In Access 2007, on the Report Tools ribbon click Report Header/Footer on the Show/Hide group (right-most icon.)
3. Set the Top and Bottom margins to 0.5". In Access 95 - 2003, choose Page Setup from the File menu. In Access 2007,on the Report Tools ribbon, click the Extend arrow at the very bottom right corner of the Page Setup group. If your printer requires the bottom margin to be greater, try 0.3" for the top margin and 0.7" for the bottom. Use the diagram above to recalculate your heights if you need more than 1" in total: the sum of all section heights must be 11" or less for Letter, 11.666" or less for A4.
4. Right-click the grey bar called Detail, and choose Properties. On the Format tab of the Properties box, set the Height of the Detail section to 2.666" for Letter paper, 2.888" for A4.
5. Add a text box to the Detail section, to use as a counter. Give it these properties:

   	Control Source:	=1
   	Running Sum:	Over All
   	Name:	txtCount
   	Visible:	No

6. Create a Group Footer on your primary key field:
   • In Access 1 - 2003, open the Sorting and Grouping dialog (View menu).
     Select your primary key field.
     In the lower pane of the dialog, set Group Footer to Yes.
   • In Access 2007, on the Design ribbon, click Grouping in the Grouping and Totals group.
     Select the primary key field.
     Click More.
     Click Without a Header.
     Click With a Footer.
7. In the Properties box, set the Height of this group footer section to 1 inch (i.e. top margin plus bottom margin).
8. (Optional.) Add a dotted line to the middle of this section to indicate where to cut the page.
9. To suppress this group footer below the third label on the page, set the On Format property of this section to [Event Procedure].
10. Click the Build button (...) beside this. Add this line to the code window, so the event procedure looks like this:
    
        Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
            Me.GroupFooter0.Visible = (((Me.txtCount - 1) Mod 3) <> 2)
        End Sub

Explanation of the code

The hidden text box accumulates 1 for each record. When the group footer is formatted, it examines the value of the text box less 1. This yields 0 for the first record, 1 for the next, 2 for the next, 3 for the next, and so on.

The Mod operator gives for the remainder after division. Results will be 0, 1, 2, 0, 1, 2, 0, ... Every third record has the value 2, and that is the bottom one on the page. So, if the result is different from 2, we set the group section's Visible property to True. If is is not different from 2, the Visible property is False.

The technique can be easily adapted for other page sizes and numbers of slips.