Enterprise Software

How do I... Start an Access label report with any label on the sheet?


Printing labels is a snap using Microsoft Office Access' Label Wizard. Depending on the criteria, the report prints the most up-to-date data in the order you need. The only problem is wasted labels; seldom will a report use all of the labels on the last sheet. You probably just throw the last sheet away. But with a little help from Visual Basic for Applications (VBA), you can use those partially used sheets. A little code is all you need to start the label report at a specific position rather than at the top of the sheet.

The easy way, sort of...

Think of each missing label on a partially used sheet as an empty record. By adding one empty record to your table to represent each missing label, you can force the report to start printing anywhere you like. For instance, if you have a partially used sheet with three missing labels, you could open the report's table, insert three empty records, and run the label report. If you think that's a bad idea, you're right. The chance that not a single field in the table has a required field is slight. Even if you can work around the problem, entering and deleting data directly into the table is a bad idea. Automate this process instead. Just because something's easy doesn't mean it's a good idea.

This blog post is also available in PDF form as a TechRepublic download.

The better way

Automating a label run that lets you choose a label position other than the first label requires four components:

  • A data table: The table that contains the data you want to print.
  • A label table: A copy of the data table, plus the appropriate number of empty records that represent missing labels.
  • A label report: A label report bound to the label table and not the data table.
  • A form: An unbound form that captures the position of the first available label.

Because the technique requires empty records, you shouldn't work directly with the data table. Instead, copy the data to a second table (the label table) that you use only for printing labels. The example prints data from the Customers table in Northwind, the sample database that comes with Access. To create a copy of this table do the following:

  1. In the Database window, switch to Tables.
  2. Select Customers and press Ctrl+C. Alternately, choose Copy from the Edit menu.
  3. Press Ctrl+V. Alternatively, choose Paste from the Edit menu.
  4. In the Paste Table As dialog box, enter tblCustomerLabels, as shown in Figure A, and click OK. When applying this to your work, be sure to give the label table a descriptive name that identifies its purpose. It doesn't matter whether you copy the data or just the structure.

Figure A

Create a copy of the table that contains the label data.

Open tblCustomerLabels (the label table) in Design view and set the Required property for each field to No. Delete any unique indexes by setting the Indexed property for each field to No or Yes (Duplicates OK). In addition, remove the primary key property from the appropriate field. In the example's case, that's the CustomerID field. (Select the row and click the Primary Key tool on the Table Design toolbar.) Close the table and save the changes.

When applying this technique to your own work, don't delete fields, but do remember to reset all property settings that generate an error if you leave a field blank or enter a duplicate value (even no value is a value as far as Access is concerned).

Use the Label Wizard to generate a label report using tblCustomerLabels. Do not use Customers (the data table). Add the following fields to the label report: CompanyName, ContactName, Address, City, Region, PostalCode, and Country. Save the report as rptCustomerLabels.

To create the form in Figure B, open a new, unbound form and insert one text box and two command buttons. Refer to Table A for property settings. Save the form as frmCustomerLabels.

Figure B

Use this form to determine where the label report begins printing.

Table A

Object

Property

Setting

form Scroll Bars Neither
Record Selectors No
Navigation Buttons No
text box Name txtStart
  Validation Rule >0
command button Name cmdPrint
Caption Print
command button Name cmdCancel
Caption Cancel

With the form still in Design view, click the Code button on the Form Design toolbar to open the form's module. Enter the code in Listing A and Listing B. Check the library references to make sure ActiveX Data Objects (ADO) is checked. To do so, choose References from the Tools menu. If Microsoft ActiveX Data Objects 2.x Library isn't checked, as shown in Figure C, check it, and click OK. (ADO is the default library in Access since version 2000.)

Listing A

Private Sub cmdCancel_Click()

'Reset and take no further action.

Me!txtStart.Value = 1

End Sub

Listing B

Private Sub cmdPrint_Click()

'Pass table with label data, position for first label, and

'label report.

Dim bytPosition As Variant

Dim bytCounter As Byte

Dim rst As New ADODB.Recordset

Set rst.ActiveConnection = CurrentProject.Connection

rst.Open "SELECT * FROM tblCustomerLabels" _

, , adOpenDynamic, adLockOptimistic

'Delete previous label data.

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE FROM tblCustomerLabels"

'Add one empty record for each missing label.

bytPosition = Nz(Me!txtStart.Value, 0)

For bytCounter = 2 To bytPosition

rst.AddNew

rst.Update

Next

'Update label data.

DoCmd.RunSQL "INSERT INTO tblCustomerLabels " _

& "SELECT * " _

& "FROM Customers"

'Open label report.

DoCmd.SetWarnings True

DoCmd.OpenReport "rptCustomerLabels", acViewPreview

rst.Close

Set rst = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

rst.Close

Set rst = Nothing

DoCmd.SetWarnings True

End Sub

Figure C

ADO must be referenced for the code to work.

Using the form

Now, you can use the form to begin a label report at any label position. First, close any open tables or reports. Then, with the form in Form view, enter the value that represents the position of the first available label on a partial sheet. For instance, if the first two labels are missing, enter 3, as shown in Figure D. That way, the report will begin printing at the third label.

You must enter a value that's greater than 0. The report handles 0, but a negative number will return an error. There's no reason why you'd want to enter a negative number, but someone might do so accidentally.

After entering a value, click the Print button. Doing so executes the button's Print event (Listing B). If you receive an error about displaying data, just click OK and ignore it.

Figure D

Enter the position of the first label you want to use.
First, the code deletes the records from the previous label run. Then, using the value you entered, a For loop appends an empty record to tblCustomerLabels for each missing label. Next, the code appends all the data from Customers into tblCustomerLabels. That way, the label report always has the most up-to-date data. You could limit the data that's copied, but doing so requires more work and isn't necessary unless you're retrieving data across a busy network or working with thousands of records. Finally, the code opens the label report in Print Preview, skipping the missing labels, as shown in Figure E. Clicking Cancel sets txtStart's value to 1 and takes no further action.

Figure E

Force Access to start printing labels where you want.

You might notice that the report prints a comma character for each missing label. That's because the label report contains a literal comma value between the City and Region fields. Not every report contains literal values, but some do. You could go to the trouble to inhibit the character, but doing so is more work than it's worth.

If you enter a value that's greater than the number of labels on a single sheet, the report simply accommodates. For instance, if the sheet contains 15 labels, and you enter 17, the report will begin printing on the second label on the second sheet of labels. Most likely, you won't want to do this. This example doesn't restrict the values you can enter because there are so many possibilities. You can include this check by adding a validation rule to txtStart. For instance, if each sheet contains 15 labels, you could use the following expression:

>0 And <16

Stop waste!

Don't throw away partially used label sheets. Instead, force Access to begin a label report anywhere on the label sheet you choose. Be sure the partially used sheet of labels is the first sheet in the printer. Then, open frmCustomerLabels, enter the position of the first available label, and click OK. The code does the rest.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
dugoneill
dugoneill

Not sure if it is because I am using 2007, but I get a syntax error when trying this code in the Print command at Row 8 of the code , , adOpenDynamic, adLockOptimistic

m-trout
m-trout

WOW! This is just what I needed!! However, it's a shame that you cannot feed in individual cards (ex: business card size; similar to the label printing in the printer one by one when you have 1-3 left on the perforated sheet.

dharrison
dharrison

All well and good, and some useful techniques to know. However, the users should be aware that using this technique to reprint on labels this is a violation of most printer warranties. Label paper is designed to run through 1 time. This is particularly true if you have peeled off some of the labels. Partial pages are more likely to leave a sticky residue on the inner transfer belts/rollers, and the fuser in printers, and are more likely to have labels peel off the page while in the printer, adhering to components somewhere in the printer. I'm all for saving money, but you need to weight the cost of a few labels against the potential cost of parts and service on your printer. I manage a network of 150 users with about 10 networked laser printers and 20 personal inkjet printers, and I have run into this several times on both types of printers. You can usually get away with it, and you may get away with it 100 times, but that 101st time may cost you more than you saved with all the pages combined. Before you use this to reprint on used label paper, you should be aware of the potential costs.

charles.barratt
charles.barratt

Rather than having a separate table created for each different set of labels, using append queries, tidying up data afterwards and the like, there is an alternative: use the NextRecord and MoveLayout methods of the report. How? Well, in the label report's OnOpen you get the number/position to start printing at, via an InputBox or obtained by setting a value available from the report (global var or function call). You can throw in some number checking here too if you want. This gives you the start position, stored as number at the report level. In the OnFormat event of the detail check the current count against the required start, if it's less then increment the count, MoveLayout=True and NextRecord=False. There you go, the records don't start printing until you've reached the start position. There's no need to worry about those formatting issues such as ", ", "@", etc. as there are no empty records.

william.meade
william.meade

I can tell you from personal experience that you should NOT reuse label sheets. It takes hours to clean the peeled off labels from the inner workings of a printer, it's not worth the pennies of potential savings...

seabruce
seabruce

The blog version is more user friendly and I appreciate the format much more. I must admit the .pdf downloads bug me, I hate downloading them and don't understand why they weren't provided as html before.

8a
8a

Sounds simple enough, could you provide a code snippet?

rwharbin
rwharbin

Found a code snippet courtesy of Microsoft's Knowledge Base. Not only allows you to skip used labels, but also print duplicates! http://support.microsoft.com/kb/q95806 Just cut and pasted the code, and it worked great!

8a
8a

Thanks, was trying to code it myself, found what i was missing.

Editor's Picks