Microsoft KB Archive/174542

= 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:

 Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB if you are using Microsoft Access version 2.0). In Microsoft Access versions 7.0 and 97:

 In the Database Window, click the Reports tab, and then click New. In the New Report dialog box, click Report Wizard and select the Employees table as the source of the object's data. Click OK. Move the EmployeeID, LastName, FirstName, and BirthDate fields from the Available Fields list to the Selected Fields list. Click Finish.</li> On the View menu, click Design View.</li></ol>

In Microsoft Access version 2.0:

<ol style="list-style-type: lower-alpha;"> In the Database window, click the Report tab, and then click New.</li> In the New Report dialog box, select the Employees table and click Report Wizards.</li> In the Report Wizards dialog box, click Tabular, and then click OK.</li> Move the Employee ID, Last Name, First Name, and Birth Date fields from the Available Fields list to the Selected Fields list.</li> Click Finish.</li> On the File menu, click Print Preview to switch to Design View.</li></ol> </li> Click the horizontal Detail bar to select the detail section of the report. On the View menu, click Properties.</li> 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 .</li> 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.</li> 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".</li> 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</li> 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</li> Click the horizontal Detail bar to select the detail section of the report. On the View menu, click Properties.</li> 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).</li> 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.</li> <li> 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 </li> <li>On the File menu, click Close to return to the Design view of the report.</li> <li>On the File menu, click Print Preview. Note that the label appears after every third record.</li></ol>

<div class="references_section">