ACC: How to Print Labels Only on the Left Margin of a Report

• Microsoft Access versions 2.0, 7.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

When you print a column report, it is sometimes necessary to display a label for each row of detail being printed within the column. However, when the report is a multicolumn report, this will cause each new column in the multicolumn report to redisplay these labels.

Microsoft Access does not contain a report option which enables the printing of labels only along the left margin of a report. A report which requires this type of output would require modifications by the user of the report.

This article describes how to modify a report so that when a multicolumn report is printed, the labels for each row of information in the columns will be printed only down the left margin of the report.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access for Windows 95" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access version 7.0) is called Access Basic in version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

To display labels on the left margin of a multicolumn report, follow these steps:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create a new Report based on the Customers table in Design View.

3. Add 11 text boxes without labels to the left margin of the Detail section, beginning with the first text box in the upper-left corner. As you add each new text box, place it directly under the last text box.

   NOTE: To avoid the automatic creation of labels with each new text box, click the Text Box button on the toolbox. On the View menu, click Properties, and set the AutoLabel property to No.

4. Set the Width property for each text box to 2".

5. Set the Name property of the first text box to txt1, the Name property of the second text box to txt2, and so on.

6. Click the ControlSource property for the txt1 text box and set the property to the first field name in the list. Click the ControlSource property for the txt2 text box and set the property to the second field name in the list. Continue this process for all 11 text boxes.

7. To the right of each text box, create a label. As each new label is created, in the label, type the name of the field which appears in the text box that is to the left of the new label.

8. Set the Name property of the first label to lbl1, the Name property of the second label to lbl2, and so on.

9. Set the Width property of each label to 2".

10. Drag the lbl1 label directly over top of the txt1 text box, and the lbl2 label directly over top of the txt2 text box, and so on.

11. Set the Width property of the report to 2" and the Height property of the Detail Section to 3".

12. Click Page Setup (for Microsoft Access 2.0, click Print Setup) on the File menu and set all margins to 0.5".

13. Set Orientation to Landscape (for Microsoft Access 7.0, first click the Page tab).

14. Click the Layout tab (for Microsoft Access 2.0, click More) and set Items Across to 4, set Layout Items to "Across, then Down" (for Microsoft Access 2.0, set Item Layout to Horizontal), and then click OK.

15. Add the following code to the Detail section's OnFormat Event property:

    Dim i As Integer If Me.Left <Me.Width Then

               Me.NextRecord = False
               For i = 1 To 11
                   Me("txt" & i).Visible = False
                   Me("lbl" & i).Visible = True
               Next i
           Else
               For i = 1 To 11
                   Me("txt" & i).Visible = True
                   Me("lbl" & i).Visible = False
               Next i
          End If
    
    

16. After closing the Module window, on the File menu, click Save, save the report as Report1, and then click Print Preview.

    NOTE: The Width and Height property values used throughout this example can and will vary from report to report.

Keywords          : kbusage PgmHowTo
Version           : 2.0 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto