Microsoft Access Tips for Serious Users

Provided by Allen Browne, 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:

Letter   A4
0.5" Top margin 0.5"
2.666" Detail (1st mailing slip) 2.888"
1" "--------Group Footer (spacer)-------- 1"
2.666" Detail (2nd mailing slip) 2.888"
1" "--------Group Footer (spacer)-------- 1"
2.666" Detail (3rd mailing slip)

 
(Group Footer suppressed)
2.888"
0.5" Bottom margin 0.5"

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:
  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.


Home Index of tips Top