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

ID: Q153052

The information in this article applies to:

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 have a report option that enables the printing of labels only along the left margin of a report. A report that requires this type of output would require modifications by the user of the report.

This article shows you how to modify a report so that when you print a multicolumn report, the labels for each row of information in the columns is printed only along 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 your version of the "Building Applications with Microsoft Access" manual.

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

MORE INFORMATION

To print 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 and 97, first

    click the Page tab).

14. Click the Columns tab (for Microsoft Access 2.0, click More) and set

    Number of Columns to 4, set Column Layout to "Down, then Across" (for
    Microsoft Access 2.0, set Item Layout to Horizontal), and then click
    OK.

    NOTE: In Microsoft Access 95, click the Layout Tab, and set Items
    Across to 4, and Layout Items to "Across, then Down"

15. Set the detail section's OnFormat property to the following event

    procedure:

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

Additional query words:

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

Last Reviewed: November 20, 1998