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:

      a. In the Database Window, click the Reports tab, and then click New.

      b. In the New Report dialog box, click Report Wizard and select the
         Employees table as the source of the object's data. Click OK.

      c. Move the EmployeeID, LastName, FirstName, and BirthDate fields
         from the Available Fields list to the Selected Fields list.

      d. Click Finish.

      e. On the View menu, click Design View.

     In Microsoft Access version 2.0:

      a. In the Database window, click the Report tab, and then click New.

      b. In the New Report dialog box, select the Employees table and
         click Report Wizards.

      c. In the Report Wizards dialog box, click Tabular, and then click
         OK.

      d. Move the Employee ID, Last Name, First Name, and Birth Date fields
         from the Available Fields list to the Selected Fields list.

      e. Click Finish.

      f. 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 "Detail1" (without the quotation marks).

  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:

   ARTICLE-ID: Q120842
   TITLE     : ACC2: How to Print Blank Line Every Nth Line in a Report

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