Data Management

How do I... Select records for a dynamic label report in Access?


Generating reports is a big part of most database applications. Generally, a query or filter does a good job of passing only the data you want to see to the report. But it isn't always that easy. There might not be any apparent logic behind the records you want to print. For instance, printing mailing labels isn't always a full batch or nothing job. Suppose you want to send a catalog to just a few new customers. When that happens, there's no easy way to limit the records you print. Printing all of them is an option, but it's inefficient. Fortunately, with just a little effort, you can create a form that lets you selectively choose which records make it to a label report. In truth, you could use this technique with any report.

Note: This information is also available as a PDF download.

An overview

You'll need data, a query, a form, a (label) report, and some code. The downside is that by simplifying the solution, you must denormalize a table. Generally, denormalizing data is a bad idea. However, you can break the rules if you have a good reason for doing so and you don't put the data's integrity at risk.

The query retrieves data for the label report. It doesn't need to retrieve all the underlying fields. Both the form and report are bound to the same query. The form displays only enough data for the user to make choices. It needn't display all the retrieved data. A user selects records, one by one, for printing using the form. Clicking a button populates and displays the report with only those records selected in the form.

Building the pieces

The first step is to add a Yes/No field to the table that contains the data. As mentioned, doing so denormalizes the data a bit. In this case, it's a harmless trade-off, as this one field won't put the data at risk. The example uses the Customers table in Northwind, the sample database that comes with Access. (To preserve the database, work with a copy of the table or import the tables into a blank database.)

Open the Customers table in Design view and add a Yes/No field named PrintLabel. Inserting the new field above the existing fields, as shown in Figure A, will make things a bit easier, but it isn't necessary. If you insert the field below the existing fields, you'll need to rearrange form and report controls if you use a wizard to create them.

Figure A: Add a Yes/No field to the data table.

Next, base a query on the Customers table and name it qryCustomerLabels Add the following fields to the query: PrintLabel, CompanyName, ContactName, Address, City, Region, PostalCode, and Country. When applying this technique to your work, be sure to include all the data the report needs.

Use the Report wizard to create a label report on qryCustomerLabels. Select the query in the Database window and then choose Report from the New Object button on the Database toolbar. Select Label Report and click OK, and the wizard will walk you through the process. You must include the PrintLabel field in the report. That might seem a bit odd because most likely you won't display this value, but the technique requires the field. Name the report rptCustomerLabels.

With the report in Design view, set the report's Filter property to the following expression:

PrintLabel = -1

In addition, set the report's Filter On property to Yes. Consider setting the PrintLabel control's Visible property to No. Depending on the report's purpose, you probably won't want to see it. Save the changes and close the report.

Next, base a Tabular form on qryCustomerLabels. To do so, select the query in the Database window and then choose Form from the New Object button. In the New Form dialog box, choose Form Wizard and click OK. As before, the wizard will walk you through the process. The example form displays only the PrintLabel and CompanyName fields in a Tabular form. Include as much data as the users need to make the decision to print (or not).

With the form in Design view, add a command button to the form's footer. To open the footer, choose Form Header/Footer from the View menu. Name the button cmdPrint and enter Print Label as the control's Caption property. Next, insert an unbound text box into the footer, name it txtCount, and enter the following expression as the control's Control Source property:

=Sum(PrintLabel)

Set txtCount's Visible property to No. This is another control users won't need to see. Finally, set the following form properties to improve the form's appearance:

  • Caption: Select records to print
  • Scroll Bars: Vertical Only
  • Record Selectors: No
  • Navigation Buttons: No
Open the form's module by clicking the Code button on the Form Design toolbar. Then, enter the procedure shown in Listing A. Return to the form in Access, save it, and close it. Listing A
Private Sub cmdPrint_Click()

'Send selected records to label report.

On Error GoTo ErrHandler

Me.Recalc

If Me!txtCount = 0 Then

MsgBox "Please select a record to print.", vbOKOnly, "Error"

Else

DoCmd.OpenReport "rptCustomerLabels", acViewPreview

End If

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, _

vbOKOnly, "Error"

End Sub

Putting it all to work

Open the from in Form view. Use the vertical scroll bar to browse the records. To choose a record for printing, check the PrintLabel control. For instance, Figure B shows the records for Around the Horn and Bon app' selected. Checking this control sets that record's PrintLabel value to -1 (True) in the Customers table.

Figure B: Select records you want to print. After selecting all the records you want to print, click the Print Label button. This control's Click procedure checks the value of txtCount -- that's the invisible text box control in the form's footer. When no record is selected, the value is 0, and the procedure displays the message box shown in Figure C. In this case, the value isn't 0, so the procedure executes the OpenReport method, which opens the label report in Print Preview. As you can see in Figure D, the report's Filter property retrieved only those records from Customers where the PrintLabel value is -1 (True).

Figure C: If no record is selected, the form won't open the report.

Figure D: The report displays only the previously selected records.

There's little error handling in the module. Test this technique thoroughly in your own database and add appropriate error handling. In addition, if you open the report from the Database window, it will display an error value because no record in the underlying data source meets the filter's condition.

An easy enhancement

The PrintLabel control is bound to the table's PrintLabel Yes/No field. That means the form opens showing the selections you made the last time you used it. That might not matter, but most likely, you'll want to begin with cleared check box controls. If that's the case, you'll need an update query and a bit more code.

Figure E shows an update query that sets each PrintLabel value of -1 to 0. To display the Update To row, choose Update Query from the Query menu. Save the query as qryCustomerLabelsReset. Next, add the procedure in Listing B to the form's module and save the form.

Figure E: This update query resets the table's PrintLabel values. Listing B
Private Sub Form_Close()

'Clear PrintLabel field.

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryCustomerLabelsReset"

DoCmd.SetWarnings True

End Sub

Select a few records and close the form. Doing so, runs the update query, which resets PrintLabel values of -1 to 0. Open the form and you'll see that all the PrintLabel values are unchecked.

Easy reporting

There are many ways to filter a report. Allowing users to choose seemingly unrelated records is a flexible and easy option for those times that a query or filter won't work. This technique takes an untraditional approach by using expressions and objects as opposed to lots of code. Doing so makes this technique available to non-developers.


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 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 published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

About

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.

3 comments
saramartin
saramartin

Well written post, well researched and useful for me in the future.I am so happy you took the time and effort to make this. See you around http://www.bayut.com

seinchan
seinchan

I have followed what are stated. Wow. work good.. but i have another question. instead of selecting the records that we want in order to generate report, how to do if want to select fields?

ssharkins
ssharkins

Letting users select fields requires a much larger net, but the easiest way is to prepare a fixed report that includes all the possible fields, but you'll have to find a design that still looks Okay when fields are missing, and that's difficult to say the least. To answer your specific question though -- the easiest way to offer a list of fields is using a list box control's Field List Row Source Type property.

Editor's Picks