Access Database Forms: How To Print A Record In Access Through A Form

Access Database Forms: How To Print A Record In Access Through A Form

Interacting with Microsoft Access database forms 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?

access database forms how to print a record

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…

  1. Open your newly created form in design view (using my order form).
  2. 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).
  3. 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.
  4. 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.
  5. On the Other tab, set the Name property to something like: cmd_PrintCurrentOrder.
  6. 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.
  7. On the Event tab, set the On Click property to: [Event Procedure].
  8. Click the Build button (…) beside this. Access opens the VBE (Visual Basic Editor) window for your VBA code.
  9. 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.

access database forms how to print a record vba code

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.

This entry was posted in Forms, MS Access, Reports, VBA and tagged , , , , . Bookmark the permalink.

9 Responses to Access Database Forms: How To Print A Record In Access Through A Form

  1. Ben says:

    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?

    Ben

  2. AYENI DANIEL says:

    Please i entre your code to my access database but show an error handler
    please what should i do.

  3. Ben says:

    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.

  4. YOKE-YIN says:

    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.

  5. Ben says:

    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.

  6. Tyronne says:

    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.

  7. Ben says:

    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.

  8. Ben says:

    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.
    Ben

  9. alex u says:

    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”
    DoCmd.RunCommand acCmdSaveRecord

    stLinkCriteria = “[Cert #]=” & Me![Cert #]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Me.Refresh
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.PrintOut acSelection, 1, 1, acHigh, 1, 0

    Else
    If CurrentProject.AllForms(“PT Cert”).IsLoaded = True Then
    DoCmd.Close acForm, “PT Cert”, acSaveNo
    End If

    End If
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.