Interacting with Microsoft Access database form provides that rich, smooth and harmonious way to control data with functionality.
One popular feature is the ability to call another object namely a report for the active/current record and either preview or print a hard copy avoiding the extra steps of navigating and finding the same record via the database or navigation window.
So, how do you print just the one record you are viewing via the form?
You will first need to create your final report, setting the correct layouts for printing. Ideally, learn to use the primary key value that will uniquely identify the record from the form, and open the report with just that one record passing it as criteria to the open report.
Access Database Forms: How To Print A Record In Access Through A Form – The Steps
I’m going to use an order form which also contains a sub form for the items of the order transaction (see the main image above that contains a a button ‘Print Current Order‘).
This will open an Access report in print preview mode ready for printing with order and the details to the current order seen via the form – all with some VBA code below…
- Open your newly created form in design view (using my order form).
- Locate and click on the command button in the toolbox (pre Access 2007) or on the Controls group of the Design ribbon (post Access 2003), and click on your form (in my example, I’ve added it to the top right corner in the form header section).
- If the wizard starts, cancel it. It will not give you the flexibility you need and depending on which version you are currently using, it may create an embedded macro which is definitely not required.
- If the Properties window is not visible, then right-click on the new command button, and choose Properties. Access opens the Properties box/ Properties sheet.
- On the Other tab, set the Name property to something like: cmd_PrintCurrentOrder.
- On the Format tab, set the Caption property to the text you wish to see on the commend button (I’ve used the caption ‘Print Current Order’), or the Picture property if you would prefer a printer or preview icon from the library.
- On the Event tab, set the On Click property to: [Event Procedure].
- Click the Build button (…) beside this. Access opens the VBE (Visual Basic Editor) window for your VBA code.
- Type the code shown below into the procedure. Make sure your ID field is the name of your primary key field (which you should have!), and your report with the name of your report.
Some additional comments to the code example above:
If the primary key is a Text type field (not a Number type field), you need to add extra quotes: str_Where = “[Order ID] = “”” & Me.Order_ID & “””” as quotes are also reserved for string values in SQL statements too.
The report will not filter correctly if it is already open. Make sure it is closed before running this procedure or add VBA code to handle this check.
If you want the report to print the report and bypass the preview mode, then replace acViewPreview with acViewNormal constant.
The only real action for this procedure sits in the last If block (for the false response) calling the report and passing the unique identifier into the where parameter. All remaining code simple handles the condition for a new or edited record state to handle errors gracefully.
Of course, you can add your own error handlers and enhance the code further – Access database forms are very flexible indeed! This is a just a starter highlighting how effective Access VBA can be.
So now anyone can learn how to print a record in Access with simple VBA code!
Anyone like to add some of their own code example to share with others? Please use the comments reply box below.
Tags: Access Database Form, access database forms, access form designs, access vba code, how to print a record in access, printing in access
please help. I had a code similar to this, in 2003 that would look up the value of the primary field and find it in a separate form and print out ONLY the record or entry corresponding to that value. But we recently moved to access 2007 and I can’t seem to get this code to work anymore please help…
i have tried using a report instead of a form for the code to work.. but it still doesn’t work
here is my code.
Private Sub Toggle46_Click()
If (Me.Toggle46) Then
DoCmd.OpenForm “PT Cert”
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “PT Cert”
stLinkCriteria = “[Cert #]=” & Me![Cert #]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection, 1, 1, acHigh, 1, 0
If CurrentProject.AllForms(“PT Cert”).IsLoaded = True Then
DoCmd.Close acForm, “PT Cert”, acSaveNo
I will have a quick look at your code – nothing to obvious stands out as to why it shouldn’t work!
I will send you a reply to your email and post my finding s here soon.
Thanks for your enquiry.
Found no errors and the code worked.
I had to re key in the double quotes again but that shouldn’t have been the issue if it compiled from the originl version.
Code works great for me. My only problem is that my form has a title header, and the header only prints on the first page. We can create several entries, and print all the new ones, but header only shows up on the first one.
If the header appears on the first page only, that is probably because you have added the label to the report header section and not the page header section which repeats on each page.
Everything works well but I don’t understand why at the bottom of the page shows “page 1 of 3” . I have 3 records saved, does it mean it will print all three records?
Thank you for your help.
Hi, It means there are three pages for the selected record and that perhaps your form is too large and may need to be adjusted and resized to fit onto one page.
Please i entre your code to my access database but show an error handler
please what should i do.
Hi, Have you entered the correct name conventions for your example? Note the square brackets, double-quotes and other markings which can throw an error if not set correctly and assuming you have used your control names and just followed my example code in the post.
What is the error and where did you see it appear?