This article is also available as a PDF download.
As a rule, you won’t give users access to tables and
queries. Instead, users usually browse and interact with data via a form.
Occasionally, they may want to print data in Form View, which Access supports–with
a few limitations. Clicking the Print button or choosing Print from the File
menu prints all the records in the underlying recordset, not just the current
record.
To print only the current record, they need to choose
Selected Record(s) in the Print Range section of the
Print dialog box. Most users just aren’t familiar enough with Access to know
how to do that on their own. Of course, you can explain it to them, and if they
need to print individual records only occasionally, that’s probably an adequate
solution. But if they print single records often, the extra steps are going to
become a nuisance, quickly.
An automated single record print
You can easily automate the process of printing a single
record using the form shown in Figure A.
The form is bound to the Employees table in Northwind, the sample database that
comes with Access, but you can use most any form. Simply add a command button
to the viewing form and then the user can click the button on the form instead
of using the built-in printing options. Most likely, you’ll inhibit those
options anyway. This method’s has one requirement: The table must include a
field that contains a unique value for each record. A single-field primary key
or an AutoNumber field will do nicely.
|
Figure A |
 |
| Users can click the Print Record button to print just the current record. |
Start by opening the example form in Design View and inserting
a command button. Name the button cmdPrint and enter Print Record as its Caption property. Next, click the Code button
on the Form Design toolbar to launch the form’s module and enter the code in Listing A. Return to Access so you can
save and close the form.
Listing A
Private Sub cmdPrint_Click() 'Print current record 'using rptEmployees. If IsNull(Me!EmployeeID) Then MsgBox "Please select a valid record", _ vbOKOnly, "Error" Exit Sub End If DoCmd.OpenReport "rptEmployees", , , _ "EmployeeID = " & Me!EmployeeID End Sub
It’s important to note that the passed EmployeeID value is a
numeric value, so there’s no need to delimit the field reference. When relying
on a text or a date field, use the apostrophe (‘) or pound (#) character,
respectively, to delimit the value in the following forms:
"EmployeeID = ' " & Me!EmployeeID & " ' " "EmployeeID = # " & Me!EmployeeID & " # "
You’ll also need a simple report, such as the one in Figure B. (When you open this bound
report from the Database window, it displays all of the data source’s records.)
You don’t have to display the primary key value in the report, but you can. Ideally,
you’ll bind the form and report to the same data source, but it isn’t
necessary. After creating the report, save and close it.
|
Figure B |
 |
| This report can display all the employees or just one employee. |
Now you’re ready to print a single employee record. To do
so, open the example form in Form View and browse the records. To print a selected
record, simply click the Print Record button. Doing so passes the current
record’s primary key value (in the case of this example, that’s the EmployeeID
field) to the report via the OpenReport method. The report retrieves only the
record whose EmployeeID matches the passed value. For instance, with the record
for Janet Leverling current, the subprocedure passes her EmployeeID value of 3
to the OpenReport method. As a result, that method’s where argument evaluates as follows:
"EmployeeID = " & Me!EmployeeID EmployeeID = 3
Access sends the current record immediately to the printer
without displaying the report. The printout is similar to the report shown in
Figure B, but it contains only one record. By default, the OpenReport’s view argument is the intrinsic constant
acViewNormal, which sends the report directly to the printer. To see the
report, use the acViewPreview constant instead.
The subprocedure won’t print a blank record but it will
print a partial record. If the primary key field contains a value, Access will
print the record; if the primary key field is null, you’ll see the message box
shown in Figure C. You can try this
yourself by browsing to a new record and clicking the Print Record button.
|
Figure C |
 |
| Access won’t print a blank record. |
Print me! Print me!
Access will let users print data while browsing records in
Form View, but it prints all of the records in the underlying recordset, not
just the current one. By adding the command button and subprocedure discussed here,
you can provide users with a quick alternative to the built-in print routine–a
print button that prints only the current record.
About the author
Susan Sales Harkins is an independent consultant and the
author of several articles and books on database technologies. Her most recent
book is Mastering SQL Server 2005 Express,
with Mike Gunderloy, published by
Sybex. Other collaborations with Gunderloy include 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 at http://www.databaseadvisors.com.
You can reach her at ssharkins@setel.com.
