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 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 : WINDOWS:2.0,7.0
Platform : WINDOWS
Issue type : kbhowto