Microsoft KB Archive/174542

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 15:37, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


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

Article ID: 174542

Article Last Modified on 1/20/2007



APPLIES TO

  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q174542

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


SUMMARY

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:

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

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


Additional query words: inf

Keywords: kbhowto KB174542