How do you use labels in access?

Printing labels is simple if you need only one label for each record in a table or query; a wizard does all the work. But if you need to print more than one label for a single record or multiple records, the wizard is inadequate. For this type of printing task, you’ll need a few additional objects and some Visual Basic for Applications (VBA) code. In this article, I’ll show you how to print one or more labels for a single record. Next month, I’ll show you how to print one or more labels for one or more records.

I’m using Access 2016 on a Windows 10 64-bit system but the objects and code will run in earlier versions through 2003. For your convenience, you can download the example .accdb or .mdb file. This database isn’t appropriate for Access Web Apps or Access Web Services. This article assumes you know how to create a table and use wizards to create forms and reports.

In a nutshell

We’ll start with the table that stores the data you’ll be using in the label report. In this case, we’re using the Customers table from Northwind. (You can use any table you like.) You’ll base a simple form on this table so you can choose the record and specify the number of labels you need. For instance, using the form, you could print five labels for the customer Around the Horn or 15 labels for the customer Familia Arquibaldo.

You’ll make a copy of the table that stores the label information to create a temporary table and base a label report on it. The report doesn’t need all the records from the original table; it needs only the records you want to print. Finally, you’ll need a VBA procedure that runs the show.

More about Software

• Get Microsoft Office Pro and lifetime access to 5 top apps for $59.99
• How to return first and last times from timestamps in Microsoft Excel
• Checklist: Microsoft 365 app and services deployments on Macs
• Google Workspace vs. Microsoft 365: A side-by-side analysis

Create the temporary table

The temporary table is based on Customers (from Northwind). It should share the common fields necessary for the labels, but not the data. The VBA code we’ll add later will add the records used in the label run. Create the temporary table manually or copy the Customers table as follows:

1. Right-click Customers in the Navigation Pane and choose Copy.
2. Right-click in the Navigation Pane and choose Paste.
3. Enter the name Temporary Customers.
4. Select the Structure Only option (Figure A).
5. Click OK.

Figure A

If you copied the Customers table, open the copy in Design view and delete the primary key. In addition, make sure none of the fields is required and none has an index. The code will duplicate records, and these table properties will generate errors. Don’t worry if the temporary table contains fields you don’t need for the label report. You can delete them if you like, but they’re harmless.

Create the label report

Next, use the Labels report wizard to generate a report on the temporary table, Temporary Customers. Add the following fields: CustomerID, CompanyName, Address, City, Region, PostalCode, and Country. Name the report Customer Label Report (Figure B). In Access 2016, the wizard is in the Reports group on the Create tab. In Access 2003, use the Report Wizard.

Figure B

Create the form

Use the Form Wizard to create a simple form based on the Customers table (not the temporary table). The form will display all the records in the Customers table so you can choose. Remove unnecessary fields or not-the code will reference only those fields you want to include in the label report. To customize the form to fit the task, add a text box, a command button, and some code as follows:

1. Open the form in Design view.
2. Add a text box control to the Form Header section and name it txtNumberofLabels.
3. Enter the caption text Number of Labels.
4. Add a command button; name it cmdPrintLabels and enter a caption of Print Multiple Labels.
5. With the button selected, click the Event tab in the Property sheet.
6. Choose [Event Procedure] from the On Click’s dropdown list (Figure C) and click the Builder button to open the form’s module.
7. Enter the code in Listing A. Don’t copy and paste from the web page. Copy it from the downloadable database file or import the .cls module, also included in the download. If you’re working with your own data, be sure to update the field and table names, accordingly.

Figure C

Listing A

Private Sub cmdPrintMultipleLabels_Click()

'Print multiple labels for current record.

Dim i As Integer

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

'Delete previous label data.

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE FROM [Temporary Customers]"

DoCmd.SetWarnings True

On Error GoTo errHandler

'Catch blank control.

'If set to 0, label report is blank but runs.

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

rst.Open "[Temporary Customers]", CurrentProject.Connection, adOpenDynamic, _ adLockPessimistic

'Adds duplicate records for selected company using input value.

For i = 1 To Me!txtNumberOfLabels.Value

With rst

.AddNew

!CustomerID = Me.CustomerID

!CompanyName = Me.CompanyName

!Address = Me.Address

!City = Me.City

!Region = Me.Region

!PostalCode = Me.PostalCode

!Country = Me.Country

.Update

End With

Next i

'Opens report.

DoCmd.OpenReport "Customer Label Report", acViewPreview

rst.Close

Set rst = Nothing

Exit Sub

'Error handling routine.

errHandler:

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

DoCmd.SetWarnings True

rst.Close

Set rst = Nothing

End Sub

Run the label report

Return to the database and open the form in Form view so we can use it to generate a custom label report. Earlier, I told you we’d use the built-in filter to select a company. If you like, you can automate this process, but to simplify the example, do the following:

1. In the Sort & Filter group, choose Filter By Form (Figure D) from the Advanced dropdown.
2. From the CustomerID dropdown, choose a customer such as BERGS (Figure E). Don’t worry if the CustomerID control displays a value other than the one shown; the feature remembers the last filter.
3. Click Toggle Filter in the Sort & Filter group and Access populates the form with the selected customer’s data.
4. Enter the number of labels you want to print, say 5, in the Number Of Labels text box (Figure F).
5. Click the Print Multiple Labels button to generate the label report shown in Figure G.

Figure D

Figure E

Figure F

Figure G

How the code works

Fortunately, the code that runs the show is simple. First, a SQL DELETE statement deletes any existing records from the temporary table (from a previous session). If you omit this statement, you can build a custom report using more than one customer–but at some point, you’d have to clear the table for the next printing task.

The If statement checks the txtNumberofLabels text box for a value. If the control is blank, the code displays a warning message, selects the control, and quits. If the control contains 0, the code displays a blank report. This behavior is something you might want to omit by adding a second If statement or an OR statement to the existing If’s condition.

After opening a Recordset object based on the Temporary Customers table, the For loop uses the value in txtNumberofLabels to add a record for each label. For example, if you enter the value 5, the For loop adds five records.

At this point, the Temporary Customers table contains a record for each label. Opening the report generates the appropriate label report. The error-handling routine is generic; you might want to customize it a bit more to fit your needs.

Print multiple labels

You can add this simple technique for printing one or more labels for a single record to almost any database. Next month, we’ll tackle printing one or more labels for more than one record.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at [email protected].