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:
- In the Database window, switch to Tables.
- Select Customers and press Ctrl+C. Alternately, choose Copy from the Edit menu.
- Press Ctrl+V. Alternatively, choose Paste from the Edit menu.
- 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.
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.
Use this form to determine where the label report begins printing.
Private Sub cmdCancel_Click()
'Reset and take no further action.
Me!txtStart.Value = 1End Sub
Private Sub cmdPrint_Click()
'Pass table with label data, position for first label, and
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.RunSQL "DELETE FROM tblCustomerLabels"
'Add one empty record for each missing label.
bytPosition = Nz(Me!txtStart.Value, 0)
For bytCounter = 2 To bytPosition
'Update label data.
DoCmd.RunSQL "INSERT INTO tblCustomerLabels " _
& "SELECT * " _
& "FROM Customers"
'Open label report.
DoCmd.OpenReport "rptCustomerLabels", acViewPreview
Set rst = Nothing
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Set rst = Nothing
DoCmd.SetWarnings TrueEnd Sub
ADO must be referenced for the code to work.
Using the formNow, 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.
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.
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
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 email@example.com.