Skipping Over Used Mailing Labels

Suppose that you often use Access to print mailing labels on individual sheets. Each time you print a partial sheet of labels, you end up with some extra, unused labels on the sheet. If you reuse that sheet of labels in the printer, Access prints right on the missing labels. Basically, you can't reuse a sheet of labels that's already partially used. That's not good because labels aren't cheap.

A solution to the problem is to pop up a dialog box like the one shown in Figure 8-5 just before Access is about to print the labels. There, the user can specify how many empty places are on the first sheet. Then the user clicks the Print button. Access prints the labels and skips over the places left behind by used labels. No more wasted labels!

Figure 8-5:

Skip Labels Form form.

Figure 8-5:

Skip Labels Form form.

The solution to the problem requires a form and some VBA code. The form is needed because you need some way of telling the procedure how many labels to skip. In the example shown in Figure 8-5, the form itself is named SkipLabelsForm. The control in which the user types the number of labels to skip is named LabelsToSkip. The form also contains a Cancel button and a Print button named CancelBttn and PrintBttn, respectively, to which you can tie code later. Figure 8-6 shows the exact name of the form and controls in Design view.

The procedure you're about to create doesn't print labels. Your database needs a report for that. You can easily create a report for printing labels via the Access Label Wizard. In Access, click a table or query in the Navigation pane from which the report gets names and addresses. Click the Create tab, and then click Labels in the Reports group to start the Label Wizard. Follow the instructions to create a report for the desired labels, which fields you want on the labels, and how you want them to appear on each label. Don't forget to put spaces between the fields as you add them.

Figure 8-6:

The Skip Labels Form shown in Design

Figure 8-6:

The Skip Labels Form shown in Design

For this example, we created a label format report named Avery 8462 Labels that's bound to a query named SkipLabelsSampleQry. However, you don't use those names in the VBA code because you want your SkipLabels procedure to work with any label-printing report, regardless of which table or query that report is bound to. So within the VBA code, refer to the report that prints the labels as ReportName and to the report's underlying table or query as RecSource (see Figure 8-7).

Figure 8-7:

The Label report (Report Name; left window) and record-source (Rec Source;

right window).

Figure 8-7:

The Label report (Report Name; left window) and record-source (Rec Source;

right window).

Dressyrprogram 2019

For SkipLabels to work, it needs to pad the top of the recordsource for the report with one blank record for each label to be skipped over. For example, if SkipLabels needs to skip over seven empty spots on a sheet of labels, it inserts seven blank records at the top of the label report's recordsource. That way, when the sheet prints, the empty records get "printed" first (on the empty spots), and real data starts printing on the first available label. Figure 8-8 illustrates the basic idea.

Figure 8-8:

Blank records equal skipped-over labels.

I LabelsTempReport

mm *



. n



*A FirstlMame

- LastlMame

Addressl »




1 Tori


345 Pacific Co a :



2 Marilou


500, 999-6th Sti

3 Wilma


1121 River Roai:

MM.« MM»

4 Frankly


734N. Rainbow



5 Margaret


P.O. Box 1295

sanamjtr'»«!! OMI«.WTHD3T

6 Simpson


1370 Washingti


Haverston Squ:

8 Hortense


P.O. Box 1014

9 Penny


P.O. Box 10

O^tsto^.P* =01

10 Matilda


323 Shi re Lane

11 Scott and Natal Schumack

228 Hollywood

12 Linda


823Paseo Cane

13 Ino


1788 Port Carlo

I.W»1 1

1 & Ho Filter 1


97 Roberts Dr.

15 Dominic


45Albany Roac


cord: H i 7 of 42 ► M y |

... . Star

d» 1 l-D.

Getting those blank records to the top of the report's recordsource is no small feat. Plus, you don't want SkipLabels to insert blank records into any real tables or make changes to any real reports in your database. SkipLabels creates and works with copies of the necessary objects: It always creates a report named TempLabels report that prints data from a table named LabelsTempReport. It creates both those objects, on-the-fly, each time.

Of course, you can't write SkipLabels in such a way that it always skips the same number of labels on the same report. You need to make it flexible enough to work with any number of empty labels on any label report. To provide flexibility, treat the number of labels to skip and the report names as parameters (values that get passed to an argument). In other words, write the SkipLabels procedure so that it can be executed at any time, using the syntax

SkipLabels(ReportName, LabelsToSkip)

where ReportName is the name of the report to print, and LabelsToSkip is a number indicating the number of blank labels at the top of the page. For example, the following statement tells SkipLabels to print the report named Avery 8462 Labels and skip over the first seven used labels on the first page:

SkipLabels("Avery 8462 Labels",7)

The code required to meet all these goals isn't brief, but you don't even need to look at it if you don't want to. All you need to really know about SkipLabels is how to get it into a standard module in your own database and how to call it to work with your own labels. You can skip to the section "Calling a Procedure from an Event," later in this chapter, if you would rather skip the morbid details for now.

Was this article helpful?

0 0


  • lorena
    Why can't I reuse a label sheet?
    8 years ago
  • jamie-leigh
    How to skip used mailing labels in access 2007?
    6 years ago

Post a comment