Printing Multicolumn Reports as Mailing Labels

Access lets you print multicolumn reports. You can create a single-column report with the Report Wizard, for example, and then arrange the report to print values from the Detail section in a specified number of columns across the page. The most common application of multicolumn reports is the creation of mailing labels.

Creating a Mailing Label with the Label Wizard

You can create mailing lists with the Label Wizard, or you can start with a blank form. The Label Wizard's advantage is that it includes the dimensions of virtually every kind of adhesive label for dot-matrix or laser printers made by the Avery Commercial Products division and several other North American and overseas manufacturers. You select the product number of the label that you plan to use, and Access determines the number of columns, rows per page, and margins for the report's Detail section. You also can customize the Label Wizard for labels with unusual sizes or those produced by manufacturers who aren't included in the Wizard's repertoire. Several other manufacturers include a note that indicates the corresponding Avery label number.

To create a report with the Label Wizard, do this:

  1. graphics/report_shortcut.gif In the Reports page of the Database window, click New to open the New Report dialog.

  2. Select Customers as the data source for the labels, select Label Wizard in the list, and click OK to open the first Wizard dialog.

  3. If you're using Avery labels, select the product code. Otherwise, select the manufacturer in the list, and select the product code. Accept the Sheet Feed option if you're using laser-printer labels (see Figure 16.31). Click Next.

    Figure 16.31. The first dialog of the Label Wizard lets you select a manufacturer and then a label size available from the manufacturer.

    graphics/16fig31.jpg

  4. In the second Wizard dialog, select the font family, size, and weight for the label. The defaults 8-point Arial light make the labels hard to read. This example uses 9-point Courier New medium (see Figure 16.32). Click Next.

    Figure 16.32. Specify the printer font and its attributes in the second Wizard dialog.

    graphics/16fig32.jpg

  5. In the third Wizard dialog, select the field of the record source for the label's first row ContactName for this example and click the > button to add it to the Prototype Label text box. Press Enter to add a new line.

  6. Repeat step 4 for the CompanyName and Address fields.

  7. Select City, press >, and add a comma and a space.

  8. Select Region, press >, add two spaces, select PostalCode, and press >. Your Prototype label appears as shown in Figure 16.33.

    Figure 16.33. The Prototype Label text box displays the label design as you add fields from the Available Fields list.

    graphics/16fig33.jpg

  9. If the mailing is international, add an additional line for the Country field.

    Note

    Although the Avery 5160 label has sufficient depth to add the Country field in 9-point type, the Wizard doesn't let you add more than four lines.

  10. Click Next and specify the fields on which to sort the labels. If you added the Country field, double-click Country in the Available fields list. Double-click PostalCode (see Figure 16.34). Click Next.

    Figure 16.34. Specify the sort order for the labels in the fourth Wizard dialog.

    graphics/16fig34.jpg

  11. In the fifth Wizard dialog, type a name for the report, such as rptCustomerLabels, and click Finish to display the labels in Print Preview.

In many cases, you receive the error message shown in Figure 16.35 prior to opening the report in Print Preview. The error message for the preceding example is due to the Wizard's miscalculation of column widths, which you correct in the next section. Click OK to dismiss the message and display the labels in Print Preview (see Figure 16.36).

Figure 16.35. This error message occurs when using Avery 5160 labels, because the Wizard's page layout settings require a page width of 8.625 inches.

graphics/16fig35.gif

Figure 16.36. Print Preview in maximized window mode shows the first few labels. Type 88 or 90 in the Zoom box to check the top and side margins if your monitor is set to 800x600 resolution.

graphics/16fig36.gif

Modifying an Existing Mailing Label Report

The Wizard doesn't let you add a line for Country, so you must alter the design of the report manually. To add the Country field to the label you created in the preceding section, do the following:

  1. graphics/design_view.gif Change to Report Design view, press Ctrl+A to select all the text boxes, and move them up within one grid dot of the bottom of the Detail section header.

  2. Click outside the text boxes to deselect them, select the ContactName text box, press Ctrl+C and Ctrl+V to add a copy of the text box to the Detail section.

  3. Move the added text box directly under the Trim([City]... text box.

  4. graphics/properties_window.gif With the added text box selected, click Properties to open the Properties window, click the Data tab, and select Country in the Control Source list.

  5. graphics/view_sorting.gif If you didn't add Country as the first sorting field in the fourth Wizard dialog, click the Sorting and Grouping button to open the dialog of the same name, select PostalCode, press Insert, and add Country above Postal Code in the Field/Expression list, and close the dialog.

  6. To prepare for fixing the Wizard's column width miscalculation, press Ctrl+A to select the text boxes and move them one grid dot to the left.

  7. graphics/form_select.gif Click the Select Report button, click the Format tab of the Properties window, and replace the 2.625 Width property value with 2.583. When you move the cursor to another text box, 2.583 becomes 2.5826. Your modified design appears as shown in Figure 16.37.

    Figure 16.37. The modified label report design has the Country field added, text boxes relocated, and the width reduced.

    graphics/16fig37.jpg

    Note

    Following is the explanation of the 2.583-inch width for the label report: The Control Wizard calculates the required page width in inches as follows: 0.25 (left margin) + 3 * 2.625 (label width) + 2 * 0.125 (column spacing) + 0.25 (right margin) = 8.625. (In some cases, the right margin is 0.30, but you can change that to 0.25). You need to reduce the width of the labels so the page width is 8.5 or less. Dividing 0.125 by 3, rounding up to 0.042, and subtracting from 2.625 results in a required width of 2.583.

    The result of the preceding calculation isn't perfect, because you need to take into account the reduced column width in setting the column spacing.

  8. graphics/preview.gif Click Print Preview and zoom the report to 100% scale. Verify that label spacing is consistent. In this example, the label for Yvonne Moncada has an extra address line. The added line pushes down labels below the row with the extra line, which results in a print registration error (see Figure 16.38). Registration errors of this type are cumulative, so if more than one row of a label page has an extra line the registration problem becomes serious.

    Figure 16.38. The increased space between the first and second lines of the labels is due to an extra line in the Address field of the label for Yvonne Moncado.

    graphics/16fig38.gif

    Note

    If the right margin is set to 0.25, you don't receive the error message shown in Figure 16.35. If you receive the message, choose File, Page Setup to open the Page Setup dialog, and set the right margin value to 0.25.

  9. graphics/design_view.gif To correct the spacing problem, return to Design view, select the Details section's header bar, open the Properties window, click the Format tab, and set the Can Grow property value to No. Preventing the detail section from expanding results in all rows having the same spacing (compare Figure 16.39 with Figure 16.38).

    Figure 16.39. Setting the Can Grow property value of the Detail section of the label report prevents print registration problems. The space between the first and second rows is now the same as that between other rows.

    graphics/16fig39.jpg

  10. graphics/preview.gif graphics/print.gif Press Ctrl+S to save your design changes, change to Print Preview, and print a sample of all pages on plain paper. If your labels appear to print correctly, print the first page on label stock for a test. If registration is correct, print the remaining pages.

Note

Tests with Avery 5160 labels and a Brother HL-1440 laser printer demonstrate that the left, top, and bottom margins set by the Wizard are satisfactory. You can fit six lines of 9-point type on a one-inch deep label.


The Page Setup dialog lets you tweak the Wizard's settings and your adjustments to improve label printing registration. You specify the number of columns in a row and the number of rows on a page by selecting settings in the Columns page of the Page Setup dialog, as shown in Figure 16.40. This dialog opens when you choose File, Page Setup in either Print Preview or Report Design view. The Label Wizard sets these values for you automatically and they change when you set new printing-related property values, but you might need to tweak them for the label stock or printer you use.

Figure 16.40. The Columns page of the Page Setup dialog lets you set the printing properties for reports using newspaper (snaking) columns. You also can change the column spacing and width, and the height (depth) of the labels.

graphics/16fig40.gif

The dialog's text boxes, check boxes, and option buttons let you set the following printing properties:

  • The Number of Columns property sets the number of labels across the page. In this example, this property is set to 3, so the labels print three across.

  • Row Spacing and the Height property determine the number of labels that fit vertically on a page and the vertical distance between successive labels. If you set Row Spacing to 0, the depth of your Detail section determines the vertical spacing of the labels.

  • Column Spacing specifies the position of the left edge of columns to the right of the first column.

  • The Width property in the Column Size group overrides the left margin, and the Height property overrides the bottom margin that you establish in Report Design view only if you clear the Same as Detail check box.

  • The Down, Then Across option causes the labels to print in snaking column style. The first column is filled from top to bottom, then the second column is filled from top to bottom, and so on.

  • The Across, Then Down option, the default for Wizard-created labels, causes the labels to print in columns from left to right and then in rows from the top to the bottom of the page. This setting is preferred for mailing labels because it wastes less label stock when using continuous-feed printers to print on stock with more than one label across.

Note

The Left and Top margin settings (which you set on the Margins tab of the Page Setup dialog) specify the position at which Access prints the upper-left corner of the first label on the page. For most laser and inkjet printers, these values can't be less than about 0.25 inches. Labels designed for laser and inkjet printers are die cut so that the marginal areas remain on the backing sheet when you remove the individual labels.


Note

You might have to make minor alignment adjustments because the upper-left corner of the printer's image and the upper-left corner of the paper might not correspond exactly.


graphics/power_tools.gif

The rptCustomerLabels report is included in Report16.mdb fn the accompanying CD-ROM in the \Seua11\Chaptr16 folder.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net