Data Management

How do I... Print a label for an individual record in Microsoft Access?

<img src="http://t.cbsimg.net/i/z/200606/how_110x85.jpg" align="right" border="0" height="85" hspace="5" vspace="5" width="110" />Printing labels is a common task and simple enough if you use the <a href="http://www.techrepublic.com/blog/howdoi/?p=129" target="_blank">Microsoft Access Label</a> wizard. You can print thousands of labels at one time. What isn't as easy is printing a label for a single record. You have to identify the record and pass it to a label report. Fortunately, the process is simple. <em>This blog post is also available in PDF form as a <a href="http://www.techrepublic.com/downloads/abstract.aspx?docid=318323" target="_blank">TechRepublic download</a>.</em>

Printing labels is a common task and simple enough if you use the Microsoft Access Label wizard. You can print thousands of labels at one time. What isn't as easy is printing a label for a single record. You have to identify the record and pass it to a label report. Fortunately, the process is simple.

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

The pieces

There are a number of ways to print a single record, but the simplest is always best. You need data, a query, a form, and a report — in this case, a label report. (You could skip the query, but it's good practice to base forms and reports on queries because queries are easier to enhance and modify as your database grows.) The example uses the data in the Customers table in Northwind, the sample database that comes with Access.

The form in Figure A is based on the simple query shown in Figure B. To create the example form using Access 2007, select the query in Figure B in the Navigation Pane and then click Form in the Forms group on the Create tab. If you're using an earlier version, select the query in the Database window and choose AutoForm from the New Object button's drop-down list. To hide the CustomerID value, set that bound control's Visible property to No. In addition, add a command button to the form's header and name the button cmdPrintLabel.

Figure A

This form doesn't display the record's primary key, but the technique requires it.

Figure B

This simple query identifies the label report's data source.

The query references several fields — the CustomerID field and all other fields required by the report. However, the form doesn't display the CustomerID value. Your users won't need to see the CustomerID value, but the technique uses it.

Specifically, the form's Print Label button passes the current record's primary key value (CustomerID) to the label report, which uses that value as a filter. Most likely, you'll also use a primary key value when applying this technique to your own database, but any unique value will do. Just remember that the query, the form, and the report must all reference the filtering field.

Your form needn't display all of the data you want to print, but the query must contain them all. On the other hand, the form can display more data than you intend to print — the label report won't care.

With the form in Design view, click the Code button to open the form's module in the Visual Basic Editor (VBE). Add the short event procedure in Listing A. Don't worry that rptCustomerLabels doesn't exist yet; you'll create the label report next. If you're working with an existing report, be sure to update the OpenReport method accordingly.

Listing A

Private Sub cmdPrintLabel_Click()

'Send customer data to label report

Dim str As String

On Error GoTo ErrHandler

If IsNull(Me!CustomerID) Then

MsgBox "Can't print an unsaved record", _

vbOKOnly, "Error"

Exit Sub

End If

str = "CustomerID = '" & Me!CustomerID & "'"

Debug.Print str

'Open report in Print Preview.

DoCmd.OpenReport "rptCustomerLabels", acViewPreview, , str

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " _

& Err.Description, vbOKOnly, "Error"

End Sub

Return to Access and save the form.

Use the label wizard to generate a label report based on the same query you used to create the form. (You can use two different queries, but using the same one simplifies the example.)

In Access 2007, select the query from the Navigation Pane and then click Labels in the Reports group on the Create tab. If you're using an earlier version, select the query in the Database window and choose Report from the New Object button's drop-down list. In the resulting dialog box, choose Label Wizard.

That wizard will guide you through the process of creating the label report. Name the label report, shown in Figure C, rptCustomerLabels. Be sure to add CustomerID to the report. Remember, you don't have to display or print it; you can set its Visible property to No. Unfiltered, the report displays a label for each record in the Customers table.

Figure C

The unfiltered report displays all the customer records.

Filtering the label report

To filter the label report via the example form, open the form in Form view, select a customer, and click the Print Label command button. The button's Click event opens the label report using a filter. For instance, if the form's current record is Alfreds Futterkiste, the filtering statement evaluates to CustomerID = 'ALFKI'. As a result, the label report displays data for only Alfreds Futterkiste, as shown in Figure D.

Figure D

This label report can print all of the records or just one record.

Notice that the filtering statement uses single quotes to delimit the filtering data. That's because the Customers table's primary key field is a text field. If the filtering data is numeric, you don't need delimiters. Dates require the pound sign (#). Also, the example opens the report in Print Preview. If you want to send the labels directly to the printer, substitute acViewPreview with acViewNormal.

Single out records for printing

This technique might not seem useful if you don't print labels one at a time, but don't let the label report distract you. You can use this technique to print individual records to any report, including complex reports such as invoices, purchase orders, and so on. Just remember the following requirements for this flexible printing technique:

  • The query and report must reference all the data you want printed.
  • The query, the form, and the report must reference the unique identifying value (probably a primary key) for each record in the data source.

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 Harkins

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