How to print one or more labels for one or more Access records

Image: iStockphoto.com/PashaIgnatov

Last month's article How to print one or more labels for a single Access record combines a few objects to create a simple printing solution that lets you choose a record and print the number of labels you specify. This month, I'll show you a similar solution that lets you print one or more labels for one or more records.

This technique is more complex than last month's but it's also more comprehensive. A continuous form accepts input values that represent the number of labels you want for one, several, or even all of the records. This month's solution can handle it all—printing one or more labels for one 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, .mdb, and .cls files. (Both databases also contain last month's single record form.) This database isn't appropriate for Access Web Apps or Access Web Services. This article assumes that you know how to create a table and use wizards to create forms and reports.

More about Office

The components

The solution requires the following components:

• A permanent table of data that you might want to print to labels
• A temporary table that stores only those records you want to print
• A continuous form
• A label report
• Two VBA procedures

We'll generate the necessary components and then run through a couple of examples.

Breaking a rule

It's important to note that this solution breaks normalization rules by adding a field that doesn't directly support the permanent table's entity. The example uses the Customers table from the Northwind database. We'll add a field to this table and use it to temporarily store the number of labels to print for a record. To that end, your first step is to open the Customers table in Design view and add a Number field named NumberOfLabels, as shown in Figure A. The default properties are fine. Save the modification and close the table. (A default value isn't necessary.)

Figure A

The new field stores the number of labels.

Denormalizing the table avoids a problem inherent to continuous forms: An unbound control stores the same value for every record. By working with a form control that's bound to the table, you avoid a lot of work. In my opinion, the tradeoff is worth it. After the label report is run, code removes the input values stored in the table, so the denormalized values are always temporary.

Create the temporary table

The temporary table is based on Customers. It should contain the fields necessary for the labels, but not the data. 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 B).
5. Click OK.

Figure B

Create a temporary table based on the Customers table.

If you copy the Customers table, open the copy in Design view and delete the primary key. In addition, make sure no field is required and remove any unique indexes. These table properties will generate errors when the solution tries to duplicate records. Don't worry if the temporary table contains fields you don't need for the label report. You can delete them, but they're harmless.

Create the label report

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

Figure C

Base a label report on the temporary table.

Create the continuous form

Figure D shows a continuous form based on the Customers table. Use the Multiple Items wizard (More Forms in the Forms group on the Create tab). In earlier versions, Access uses the term continuous, not Multiple Items.

Figure D

Use this continuous form to specify the number of labels for each company.

With the form in Design view, remove extraneous controls and resize those remaining if necessary. If you're using a Ribbon version, you can disable the default Remove Layout property to work with controls individually as follows:

1. Click the Arrange tab.
2. In the Table group, click Remove Layout.

The form doesn't need to display the same data you want to print. You need only the values necessary to identify and filter the records. For instance, the form doesn't display the address because you probably don't need it to determine whether to print the label. You'd rely on the customer's name. The other fields contain good filtering choices. We'll discuss filtering later.

Add a command button to the form's header, set its Caption property to "Print Labels," and name it cmdPrintLabels. Then, click the View Code button in the Tools group and add the procedures shown in Listing A and Listing B. Note: Don't copy the code from this web page; the Visual Basic Editor (VBE) will return errors because it can't interpret all the web characters correctly. Instead, download the demonstration file or import the downloadable class module.

Listing A

Private Sub cmdPrintLabels_Click()

'Print multiple labels for current record.

'Form populates using permanent Customers table as form's record source.

Dim iTab As Integer

Dim lTotal As Long

Dim iLab As Integer

Dim rstTable As ADODB.Recordset

Set rstTable = New ADODB.Recordset

On Error GoTo errHandler

'Save current record in case user forgets to hit enter.

If Me.Dirty Then Me.Dirty = False

'Filter form's recordset to remove records with 0 label value.

Me.Filter = "NumberofLabels <> 0"

Me.FilterOn = True

' Debug.Print Me.Recordset.RecordCount

'Get record count for first For loop below. Long is required.

lTotal = Me.Recordset.RecordCount

' Debug.Print lTotal

'Close and delete records from Temporary Customers table.

DoCmd.SetWarnings False

DoCmd.Close acTable, "Temporary Customers"

DoCmd.RunSQL "DELETE FROM [Temporary Customers]"

DoCmd.SetWarnings True

'Open recordset based on temporary customers table.

'Need static cursor to support RecordCount property.

'Not currently relevant, but for future consideration.

rstTable.Open "[Temporary Customers]", CurrentProject.Connection, _

adOpenStatic, adLockPessimistic

'Populate Temporary Customers tables for label report.

'First For tracks the number of records in form recordset.

'Second For tracks the number of labels for each record.

With rstTable

For iTab = 1 To lTotal

'Add multiple records for same record.

'Second For inserts individual record multiple times.

' Debug.Print Me.NumberOfLabels.Value

For iLab = 1 To Me.NumberOfLabels.Value

.AddNew

!CustomerID = Me.CustomerID

!CompanyName = Me.CompanyName

!Address = Me.Address

!City = Me.City

!Region = Me.Region

!PostalCode = Me.PostalCode

!Country = Me.Country

.Update

Next iLab

'Move to next record in form.

DoCmd.GoToRecord , , acNext

Next iTab

End With

DoCmd.OpenReport "Customer Label Report", acViewPreview

rstTable.Close

Set rstTable = Nothing

Me.Filter = ""

Exit Sub

errHandler:

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

DoCmd.SetWarnings True

rstTable.Close

Set rstTable = Nothing

Me.Filter = ""

End Sub

Listing B

Private Sub Form_Close()

'Remove input label values from underlying table, Customers.

On Error GoTo errHandler

'Close Customers table if open and then delete NumberOfLabels values.

DoCmd.SetWarnings False

DoCmd.Close acTable, "Customers"

DoCmd.RunSQL "UPDATE Customers SET NumberOfLabels = NULL"

DoCmd.SetWarnings True

Exit Sub

errHandler:

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

End Sub

Before leaving the VBE, be sure the ADO library is referenced. From the Tools menu, choose References and verify that the latest ADO library is checked, as shown in Figure E. Then, return to Access, save the form, and close it.

Figure E

You must reference an ADO library.

Use the form

Now let's work through two simple examples. First, let's print one label for Alfreds Futterkiste, three labels for Antonio Moreno Taquería, and two for Bon app' for a total of six labels. Open the continuous form in Form view and enter the respective values, as shown in Figure F. When you're ready to run the label report, click the Print Labels button. Figure G shows the resulting label report.

Figure F

Enter the number of values for each record.

Figure G

The label report contains the appropriate number of labels for each record.

At this point, you can print the labels, or not. When you close the label report, Access returns to the continuous form with the input values still intact. You can update those values or close the form. The code in the form's Close event (Listing B) resets the NumberOfLabels field for each record in Customers to Null.

The button's code is simpler than it might appear. First, the code saves the form to ensure that the last label value entered makes it to the label report. Next, applying a simple filter removes the unmarked records. Two SQL statements then close Temporary Customers (to avoid an error if it's open) and remove records from the previous print task.

The two For loops copy the remaining records to Temporary Customers. The first loop tracks the number of records in the filtered recordset. The second loop tracks the number of labels you want for individual records. If you want three labels, the second loop inserts the current record into the temporary table three times.

After completing both loops, the temporary table contains the appropriate records to complete the label report: one label for Alfreds Futterkiste, three for Antonio Moreno Taquería, and two for Bon app'. Finally, the code opens the label report, destroys the recordset, and resets the form's filter to nothing.

If the form and report are open, close them both to work through a filtering example. Reopen the form and use the built-in Selection filter to limit the records to Mexico, as follows:

1. Double-click inside any Country field that contains Mexico.
2. In the Sort & Filter group (on the Home tab) choose Equals "Mexico" from the Selection option's dropdown (Figure H).

Figure H

Use the Selection option to filter the records.

After filtering the record, enter the following label values: 2 for Ana Trujillo Emparedados y helados and 3 for Centro comercial Moctezuma (Figure I) and click the Print Labels button. By filtering the records using natural data, you can quickly limit the number of records you need to view. You could just as easily enter the values without filtering, but filtering can make your task easier. You can filter by any field—but filter before entering label values.

Figure I

Filter the records before entering the label values.

On your own

When applying this to your own work, remember to update object names (tables, forms, reports, and fields) accordingly. The more bells and whistles you add, the more code you'll need to accommodate those enhancements. In addition, the error-handling is basic; test extensively to allow for custom responses.

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 susansalesharkins@gmail.com.

Also read...

Source: http://techrepublic.com.feedsportal.com/c/35463/f/670841/s/4e41dc49/sc/28/l/0L0Stechrepublic0N0Carticle0Chow0Eto0Eprint0Eone0Eor0Emore0Elabels0Efor0Eone0Eor0Emore0Eaccess0Erecords0C0Tftag0FRSS56d97e7/story01.htm