Data Management

How do I... Print a single Microsoft Access record in Form View?

Microsoft Access lets 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 a command button and the subprocedure presented here, you can give users an easy, one-click tool for printing only the current record.

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.


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.

22 comments
greenhart
greenhart

'* Code for button click event to print the current record showing in a form. '* It prints the form graphically, i.e. WYSIWYG '* All you have to do is select the printer. '* Have not tried with Subforms. Private Sub cmdPrintForm_Click() On Local Error Resume Next '* This can be improved - it's just to avoid "print cancelled" messages DoCmd.RunCommand acCmdPrintSelection '* Yep,this prints the current form - you don't have to select it(in Access 2007, anyway). End Sub

ChrisBuchanan
ChrisBuchanan

A lot of these print unlinked forms, but as soon as I link them, Print Preview goes blank (Headers only). Does anyone know why?

temirbek
temirbek

Mam, did you fix your space bar already? :)

rayhufault
rayhufault

i used this code but i have 2 subforms and its only printing one form and with none of the data input please help going nuts Private Sub PRINT_Click() On Error GoTo Err_PRINT_Click Dim MyForm As Form Dim record_num As Long stDocName = "Invoices" Set MyForm = Screen.ActiveForm DoCmd.SelectObject acForm, stDocName, True record_num = MyForm.CurrentRecord DoCmd.PrintOut acPages, record_num, record_num Exit_PRINT_Click: Exit Sub Err_PRINT_Click: MsgBox Err.Description Resume Exit_PRINT_Click

jayres14
jayres14

Okay so I tried using this code but was unsucessful. I need to be able to serach for a record (which I can do) and just print that single record into a form without printing the whole database. Please any help would be greatly appreciated. Private Sub cmdPrint_Click() 'Print current record 'using rpt1234. If IsNull(Me!FNLastName) Then MsgBox "Please select a valid record", _ vbOKOnly, "Error" Exit Sub End If DoCmd.OpenReport "rpt1234", , , _ "FNLastName = " & Me!FNLastName End Sub ------------------------------------------------------ FNLastName is how the form is selected via a search, however there may be a the same Last Name so each record has a Seq # that is autonumbered and not duplicated. rpt1234 is which report format I want to use.

vimal.tech
vimal.tech

Hi, This informtion is very usefull, and i want some more information about print. My requrement is User can't use Print Button in Form, they would like to use File->Print Options. If they use File->Print Option there will print all User forms details and there controls also like Buttons, TextBox etc., So i want Use MS Acess wone print option but don't print controls. Please help me...it's very urgent requriment...

khingcat
khingcat

I use Access 2003, I've tried to follow your code, but I changed the unique field's name and it said error, especially on this part: DoCmd.OpenReport "rptEmployees", , , _ "EmployeeID = " & Me!EmployeeID I changed rptEmployees with my report name INV and EmployeeID with XITE_ID Can you tell me what's wrong?

kancsel
kancsel

The article is comprehensive ,yet simple and easy to understand.

iammaxter
iammaxter

Fine so far, but I also need to e-mail that same current record in the form of a report or form. Can you help with that please.

nickwalsh
nickwalsh

This solution works fine for me: Private Sub command35_Click() 'Print current record 'using Invoice. If IsNull(Me!JobNumber) Then MsgBox "Please select a valid record", _ vbOKOnly, "Error" Exit Sub End If DoCmd.OpenReport "Invoice", , , _ "JobNumber = " & Me!JobNumber End Sub However if I change the form type to Data Entry I get a blank report printed (no data on it). If I change it back to not Data Entry it is fine. I want to print a record from a data entry sheet when the record is added. Can you help?

lbeason
lbeason

This is great. Question: Is there away to email a single report page as well?

koalajoe
koalajoe

Thank you for this good article. I struggled with this due to a complex form that needed a lot of updates, so the following works for me. You need to have a unique ID showing on the form. ----- Private Sub cmdPrint_Click() On Error GoTo cmdPrint_Click_Err DoCmd.GoToControl "ID_mytable" DoCmd.RunCommand acCmdFilterBySelection DoCmd.PrintOut acSelection, 1, 1, acHigh, 1, True cmdPrint_Click_Exit: Exit Sub cmdPrint_Click_Err: MsgBox Error$ Resume cmdPrint_Click_Exit End Sub ---- Note: - for "ID_mytable" substitute the name of your control bound to your ID. - this is for the click event of a button named "cmdPrint" - using Access 2003 - probably works with earlier versions Before running the procedure, carry out the actions manually excepting to use the page setup and print dialog so as to set the defaults for printing. Extras to the code could include turning off the filter before exiting, storing current focus and restoring before exit. See any 'gotchas' with this? Mark

rajeshemerald
rajeshemerald

May be this is what you required to print the current record you are viewing. Private Sub PRINT_Click() On Error GoTo Err_PRINT_Click Dim MyForm As Form Dim record_num As Long stDocName = "YOUR_FORM_NAME" Set MyForm = Screen.ActiveForm DoCmd.SelectObject acForm, stDocName, True record_num = MyForm.CurrentRecord DoCmd.PrintOut acPages, record_num, record_num Exit_PRINT_Click: Exit Sub Err_PRINT_Click: MsgBox Err.Description Resume Exit_PRINT_Click End Sub

JodyGilbert
JodyGilbert

Does this sound like a customization trick your Access users might appreciate? What other tweaks do you implement to make their work go more smoothly?

ChrisBuchanan
ChrisBuchanan

I have now found that linked forms will print if you change something on the form. So I'm currently trying to make an invisible change on a form on load, and empty it on close.

bryandavey
bryandavey

thanks a lot for this. it worked the first time i tried it !

drogers
drogers

everybody's tip for printing current form works except when some forms have excessive detail lines in a sub form and the print out exceeds 1 page. then all forms after that are off by that page. It appears that all the various solutions rely on all pages of a form not exceeding one page no oscar for everybody. I need a better solution for this issue M/S should have built it into a option in button set up properly. great effort. i wish i knew VB i would help more.

deepsand
deepsand

The "Discussion" forum is for matters of general discussion, not specific problems in search of a solution. Post problems such as this to the "Question" forum, rather than the "Discussion" forum. There are those who specifically seek out problems in need of a solution, and that's where they go to look for such. Furthermore, the "Questions" forum provides for your rating Helpful responses as such, so that others, with the same or a similar problem, might be able to find aid without having to necessarily begin a new Question.

haarsaker
haarsaker

Solution is in using wherecondition argument on DoCmd.OpenReport. Assuming I want to use a button, click on EVENT and add the follwing VBA code: Private Sub cmdPreviewRpt_Click() Dim strReport As String Dim strWhere As String strReport = "My report name here" strWhere = "[Name of my primary Key Field here] = """ & Me![Name of my Primary Key Field] & """" DoCmd.OpenReport strReport, acViewPreview, , strWhere End Sub If primary key field is numeric you change strWhere sentence to: strWhere = "[Name of KeyField] = " & Me![Name of KeyField]

Merlirin
Merlirin

I teach Acess 2002 classes and I am always looking for new ways to automate Access.

Editor's Picks