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.

Editor's Picks