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.
|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.
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.
|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.
|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 email@example.com.
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.