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.