A wizard makes printing labels in Access a quick task. However, the resulting report is a tad limited. You can print only one label for each record in the underlying data source. Realistically, that’s not always adequate. It’s common to print multiple labels for a single record or even multiple labels for a number of records. The wizard can’t handle that, but you can — with just a few objects and a little VBA code.
Note: This article is also available as a PDF download, along with the sample code.
Possible print tasks
Giving your users exactly what they need makes them happy and makes you look good. In the case of printing labels, your users might need to:
- Print one label for multiple records.
- Print multiple labels for a single record.
- Print the same number of multiple labels for multiple records.
- Print multiple labels, in varying amounts, for multiple records.
Most likely, you’ll want to provide a selection form for all but the first printing task. That way, the user can choose records and supply the number of labels needed for each.
A limited SQL solution
SQL can supply a rather simple solution for printing multiple records for a single record. The form in Figure A lets you print multiple labels for the current record. When you click the command button in the header, code executes a Jet SQL INSERT INTO statement that populates a temporary table, which in turn populates an existing label report.
Figure A: You might use SQL to print multiple labels for a single record.
This solution requires a data source, a temporary table, a form, and a label report. The data source, in this case, is the Customers table in Northwind (the sample database that comes with Access).
The temporary table, tblCustomerLabels, is just a blank copy of the Customers table. You can create it manually or use the Copy and Paste commands to make a copy of Customers. If you copy Customers, open the copy in Design view and remove the primary key, set all Indexed properties to No, remove any Violation Rule settings, and set all Required properties to No.
Simply put, you need a skeleton table that stores just the data. Field properties can be problematic when inserting data via an INSERT INTO statement. Just be careful — reset the properties in the copy, not the original table. Consider removing the CustomerID, Phone, and Fax fields, as the report doesn’t need them. It won’t hurt anything if you leave them, but if you apply this technique to your own databases, retrieve only the data you actually need in the label report.
Next, use the Report Wizard to build a label report on tblCustomerLabels, adding the CompanyName, ContactTitle, ContactName, Address, City, Region, PostalCode, and Country fields. Figure B shows the report, named rptCustomerLabels, in Design view.
Figure B: The structure of this label report is like any other label report.
Creating the example form is easy. Use the AutoForm Wizard to base a simple form on the Customers table. Most likely, that form will contain a related subform — you can retain it or discard it. Add a command button and a text box. (You can add them to the form’s header of footer, but that isn’t strictly necessary.) Name the command button cmdPrintMultipleLabels and the text box txtNumberOfLabels. Set the control’s Default Value property to 1 (or the amount that’s most common to your print task). With the form in Design view, click the Code button and add the code in Listing A.
Private Sub cmdPrintMultipleLabels_Click() 'Print multiple labels for current record. Dim bytCounter As Byte Dim strSQL As String On Error GoTo errHandler If IsNull(Me!txtNumberOfLabels) Then MsgBox "Please indicate the number of labels you want to print", _ vbOKOnly, "Error" DoCmd.GoToControl "txtNumberOfLabels" Exit Sub End If 'Delete previous label data. DoCmd.SetWarnings False DoCmd.RunSQL "DELETE FROM tblCustomerLabels" 'Set up label record. strSQL = "INSERT INTO " _ & "tblCustomerLabels(CompanyName, ContactTitle, " _ & "ContactName, Address, City, Region, " _ & "PostalCode, Country) " _ & "VALUES(" _ & "'" & Me!CompanyName & "', " _ & "'" & Me!ContactTitle & "', " _ & "'" & Me!ContactName & "', " _ & "'" & Me!Address & "', " _ & "'" & Me!City & "', " _ & "'" & Nz(Me!Region, "NA") & "', " _ & "'" & Me!PostalCode & "', " _ & "'" & Me!Country & "') " Debug.Print strSQL 'Populate table with records. For bytCounter = 1 To Me!txtNumberOfLabels.Value DoCmd.RunSQL strSQL Next DoCmd.SetWarnings True DoCmd.OpenReport "rptCustomerLabels", acViewPreview Exit Sub
errHandler: MsgBox Err.Number & “: ” & Err.Description, vbOKOnly, “Error” DoCmd.SetWarnings True End Sub
Within the context of this example, the form (Figure A) is simply an interface component for populating the report. Your form can be multipurpose. What matters is that the form and the report share the same fields as the source. The data source for each can contain additional fields, but they must both have the report’s fields in common.
At this point, you’re ready to use the form to populate and display the label report. With the form in Form view, browse to any record, say, Around the Horn (record 4) and enter the value 4 into the Number Of Labels field, as shown in Figure C. Then, click the command button to print the label report shown in Figure D.
Figure C: Enter the number of labels you want to print for the current record before clicking the command button.
Figure D: The label report prints the number of labels you specify in the form.
If the button’s Click event (Listing A) finds a value in txtNumberOfLabels, the code deletes all the records in tblCustomerLabels. The next bit of code concatenates the address data for the current record into a Jet SQL INSERT INTO statement. Then, the For loop executes the SQL statement. The value you enter in txtNumberOfLabels determines how many times VBA repeats the loop. If there’s no value in txtNumberOfLabels, the code displays the message shown in Figure E.
Figure E: You must enter a value to generate a report.
The SQL statement is simple enough, but it’s problematic. For instance, if a value contains a quotation mark, the SQL statement generates an error. Figure F shows what happens when trying to print labels for B’s Beverages. It’s not a good solution in a production database.
Figure F: This solution is limited and can generate an error that’s difficult to fix.
Print one or more labels for one or more records
The previous solution is simple but limited: It prints multiple labels for a single record and is prone to failure if there’s a quotation mark in the record. A more comprehensive solution would print one or more multiple labels for one or more records — taking care of every possible print task.
The continuous form shown in Figure G displays just a few fields of data and a new field named LabelTotal, which you must add to the Customers table. Normally, you shouldn’t denormalize a table, but in this case it’s efficient. By denormalizing the Customers table with this one field, you can avoid a lot of snowballing code. So the first step in this technique is to open Customers in Design view and add a Numeric table named LabelTotal. If you like, you can set the field’s Default Value to 0 and run an Update query to set that LabelTotal for existing records to 0. However, doing so isn’t necessary for the technique to work. Be sure to save the changes to Customers and close it.
Figure G: This continuous tabular form lets you print a batch of labels for multiple records.
Next, use the Form Wizard to create a continuous form on Customers but display only two fields: CompanyName and LabelTotal. Add a command button to the header and name it cmdPrintMultipleLabels. With that form in Design view, click the Code button to launch the form’s module and add the code shown in Listing B.
Private Sub cmdPrintMultipleLabels_Click() ‘Open the label report. On Error GoTo errHandler ‘Save current record in case ‘user forgets to hit enter. If Me.Dirty Then Me.Dirty = False On Error Resume Next DoCmd.OpenReport “rptCustomerLabels”, acViewPreview Exit Sub errHandler: MsgBox Err.Number & “: ” & Err.Description, _ vbOKOnly, “Error” End Sub
Base a Select query on Customers and add all the fields necessary to satisfy the label report in the grid. Also include LabelTotal and set its criteria field to >0, as shown in Figure H. Save the query as qryMultipleLabels and close it.
Figure H: This query retrieves records to be printed.
To reuse the label report from the SQL solution in the previous section, open it in Design view and make its RecordSource property qryMultipleLabels. Then, with the report in Design view, click the Code button and add the code shown in Listing C and Listing D. Save the report’s module. Listing C calls a user-defined function you haven’t created yet. Listing D cancels the report when the user fails to enter a label value.
Private Sub Detail_Print
(Cancel As Integer, PrintCount As Integer) Call PrintMultipleLabels(Me, Me.LabelTotal) End Sub
Private Sub Report_NoData
(Cancel As Integer) MsgBox "You must choose to print at least one label.", _ vbOKOnly, "Error" Cancel = True End Sub
There’s one last bit of code. While still in the Visual Basic Editor (VBE), choose Module from the Insert menu and enter the user-defined function shown in Listing E. Be sure to put the two declaration statements in the General Declarations section. Save the module and return to Access.
Dim intLabelTotal Dim intLabelCount
Function PrintMultipleLabels(rpt As Report, lbls As Long) 'Print appropriate number of labels for each record. intLabelTotal = lbls On Error GoTo errHandler If intLabelCount < (intLabelTotal - 1) Then rpt.NextRecord = False intLabelCount = intLabelCount + 1 Else intLabelCount = 0 End If Exit Function
errHandler: MsgBox Err.Number & ": " & Err.Description, _ vbOKOnly, "Error" End Function
To print one or more labels for one or more records in the Customers table, open the example’s continuous form. Notice that the LabelTotal fields are blank. Enter the appropriate values for the labels you want to print, as shown in Figure I.
Figure I: Print labels for several records.
After entering values for the labels you need, click the Print Labels button in the header. Doing so triggers the command button’s Click event (Listing B). First, the code saves the current record, just in case your user forgets to press Enter after typing that last value (which is easy to do in a continuous form). Then, the code opens the label report shown in Figure J.
Figure J: Print a specific number of labels for any record.
The report’s Record Source property is the new query, which retrieves records with a LabelTotal value — the records you marked using the continuous form (see Figure I). As the report prints each record, the Detail section’s Format event calls the duff (Listing E). This function compares the record’s LabelTotal value (lbs.) to the current record. As long as the current record is less than the total number of labels (minus 1) you need, the report prints the current record.
With flexibility comes reliability
A complex problem doesn’t always need a complex solution. The first solution for printing multiple labels is limited because it prints labels for only one record. In addition, if any data you need contains a quotation mark, it crashes. The second solution only seems complex until you actually implement it. The code is certainly less complex than the first solution’s code. That it’s more flexible and reliable is a great bonus!