ACC: How to Print a Label After Every Nth Line of a Report

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article describes how to display a label between a set number of printed lines of data on a report. For example, you can use this method to display a label after every third line of data in your report.

MORE INFORMATION

The following example demonstrates how to display a label after every three lines in a report:

1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB if you are using Microsoft Access version 2.0).



2. In Microsoft Access versions 7.0 and 97:
   
   
   1. In the Database Window, click the Reports tab, and then click New.
   
   
   
   2. In the New Report dialog box, click Report Wizard and select the Employees table as the source of the object's data. Click OK.
   
   
   
   3. Move the EmployeeID, LastName, FirstName, and BirthDate fields from the Available Fields list to the Selected Fields list.
   
   
   
   4. Click Finish.
   
   
   
   5. On the View menu, click Design View.
   
   
   
   In Microsoft Access version 2.0:
   
   
   1. In the Database window, click the Report tab, and then click New.
   
   
   
   2. In the New Report dialog box, select the Employees table and click Report Wizards.
   
   
   
   3. In the Report Wizards dialog box, click Tabular, and then click OK.
   
   
   
   4. Move the Employee ID, Last Name, First Name, and Birth Date fields from the Available Fields list to the Selected Fields list.
   
   
   
   5. Click Finish.
   
   
   
   6. On the File menu, click Print Preview to switch to Design View.
   
   
   



3. Click the horizontal Detail bar to select the detail section of the report. On the View menu, click Properties.



4. Click the Other tab, (or click Other Properties in the box at the top of the property sheet if you are using Microsoft Access version 2.0). Set the Name property to Detai1.



5. Click the Format tab, (or click Layout Properties in the box at the top of the property sheet if you are using Microsoft Access version 2.0). Change the Height property to .75.



6. Press SHIFT while clicking on each control in the detail section. When you have selected all of them, use your pointer to move the entire group down .5".



7. Add a text box to the detail section of the report and set the following properties:
   Name: txtCounter Control Source: =1 Running Sum: Over All Visible: No



8. Add another text box to the detail section and set the following properties:
   Name: txtTextLabel Control Source: ="Any text you want on your label" Top: 0" Height: .45" Visible: No Can Shrink: Yes Can Grow: Yes



9. Click the horizontal Detail bar to select the detail section of the report. On the View menu, click Properties.



10. Click the Event tab, (or click Event Properties in the box at the top of the property sheet if you are using Microsoft Access version 2.0).



11. Click to place the pointer in the OnFormat box; then, click the Build button located to the right of the box. In the Choose Builder dialog box, click Code Builder, and then click OK.



12. Type the following code.
    
    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when using this code in Microsoft Access version 2.0.
    

    
           Private Sub Detail1_Format (Cancel As Integer, _
                 FormatCount As Integer)
              If (Me![txtCounter] <> 1) And _
                    ((Me![txtCounter] - 1) Mod 3 = 0) Then
                 Me![txtTextLabel].Visible = True
              Else
                 Me![txtTextLabel].Visible = False
              End If
           End Sub 




13. On the File menu, click Close to return to the Design view of the report.



14. On the File menu, click Print Preview. Note that the label appears after every third record.

REFERENCES

For more information about formatting reports based on a set number of lines per group, please see the following articles in the Microsoft Knowledge Base:

Q120842 ACC2: How to Print Blank Line Every Nth Line in a Report

Q139046 ACC: Print Blank Line Every Nth Line in a Report (95/97)

Additional query words: inf

Keywords : FmrCdbeh FmrHowto RptLayou
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto