Improve company productivity with a Business Account.Sign Up


How do I print Access report to PDF, give PDF unique name based on report content, and save it to a special folder on my computer?

Posted on 2008-06-12
Medium Priority
Last Modified: 2013-11-27
Hi, all!
SOFTWARE INVOLVED: MS Access 2007, Adobe Acrobat Professional 8, and I also have the MS Print to PDF add-in that came out with Office 2007.
BACKGROUND: I am the registrar for a teeny-tiny, private school. I made an Access database to maintain student grades and contact information (which means I am the IT department, too). I would rate my "skills" with Access at about intermediate to low-intermediate. I have had no formal training and can only figure out VB tweaks if the instructions are explicit.
ISSUE: In an effort to scale back costs I have started uploading reports with specific names as PDF files to our website for the parents to view/print. I am looking for a way to do the following in one click of a button: 1) print the report with my Adobe PDF printer, 2) name the resulting PDF with the student whose record is open on the form (so, DoeJaneReportName2008-09SY.pdf), and 3) save the file in a folder on my computer that is uploaded to our website every night. Right now I am clicking the print button, selecting the Adobe PDF printer (because it makes smaller files than the Microsoft PDF maker add-in and a good deal of the families in the school are still on dial-up), copying and pasting a field on the form into the Adobe naming dialog box (the field might read SmithJohnPermissionSlip2008-09SY), and saving the pdf to the folder I need it in. I do these one at a timeI dont need to change thatbased on reports that open/print because of a single student record I have open on the contact form.
Thank you so much for reading! I know it was long, but I hope I included all of the pertinent information.
Question by:eBethany

Expert Comment

ID: 21772354
I had a similar project a while back that required printing to Acrobat and used the info in this link to implement it.

Here's another link of resources for your question from other apps. Most recently I have been using PDF995 to do the PDF creation.

Hope it helps.
LVL 38

Expert Comment

ID: 21773142
I have heard that MS Access 2007 natively supports pdf.  If that is the case, all you need to do is launch the Access report in preview mode and store the report name in  variable, for example strAccessRptName.
Then, put the following code behind a button.

Private Sub YourButton_Click()
DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
End Sub
LVL 38

Expert Comment

ID: 21773362
If you already have Adobe installed, and Access's native pdf add-in does not work, try using the ShellExecute method.  It may not give you everything you want, but it is easy to implement and has to be better than what you have now.

The following example uses ShellExecute on a button click event.  You need to replace the strPdfWriterPath with the path to the Acrobat.exe on your machine.

Private Sub PrintPdf_Click()
Dim strPdfWriterPath As String
Dim strFilePath As String

'Open File
'strPdfWriterPath is the path to the application object
'strFilePath is the path to the file you want to open

strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
strFilePath = Me.FilePath

Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)

End Sub
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 21773474
fcfang: Thank you for the resource!  Those links have a lot to explore.

puppydogbuddy: Wow, it looks so simple.  I will try your solution this evening and get back to you.
LVL 38

Accepted Solution

puppydogbuddy earned 2000 total points
ID: 21773553
I hope it works for my first post, I left out a backslash in the illustrative path I gave you:
change this:
              "c:PDF Reports\" & strAccessRptName & ".pdf", False
to this:
             "c:\PDF Reports\" & strAccessRptName & ".pdf", False

The above file path assumes that you have set up a folder called PDF Reports on the root directory of your  C drive.
LVL 38

Expert Comment

ID: 21773793
One more thing that just came to my mind.....if the pdf for Access 2007 is an activeX add-in and not a native Access object (as I was told), you may have to set a reference to MicrosoftPdf or something similar in the VB library references before Access can expose the pdf object and properties for you to use.


Expert Comment

ID: 21774190
Hi Bethany,
If you want to use PDF995 (which I would recommend) you will have to do some fiddling with the PDF995 INI file to get it to write to a different file name each time.
I've attached the code I used last time I worked with this utility.
I tried Acrobat and found I couldn't completely automate the printing process (at that time, a couple of years ago).
Have you considered privacy of the pupils and parents? If you are really uploading a file like that then anyone who can figure out the system (ie the pupil's classmates) can download any other pupil's report.
You might be better emailing each report as an attachment.

Hope this helps!

    Declare Function nWritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyname As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
Sub PrintToPDF(r As Report, fn As String)
    Const PROC_LOC = 9192
    On Error GoTo proc_err
    Dim sPDFINI As String
    Dim s As String
    Dim i As Integer
    Dim t0 As Long
    Dim stg As String
    stg = "Getting location of INI file"
    sPDFINI = "c:\pdf995\res\pdf995.ini"
    stg = "Setting Printer for report " & r.Name
    r.Printer = Printers("PDF995")
    nWritePP fn, sPDFINI, "Parameters", "Output File"
    stg = "Opening report " & r.Name
    Screen.MousePointer = 11
    DoCmd.OpenReport r.Name, acViewNormal
    t0 = Timer
    stg = "Waiting for report to print "
    For i = 1 To 10
    For i = 1 To 10
        If Len(fn) > 0 And Len(Dir(fn)) > 0 Then
            Exit For
        End If
        If Timer - t0 > 10000 Then
            If vbYes = MsgBox("PDF " & fn & " not created yet, continue waiting?", vbYesNo) Then
                t0 = Timer
                Exit For
            End If
        End If
    stg = "Finished waiting for report to print to " & fn
    i = MsgBox("PDF " & fn & " created: do you want to view it?", vbYesNo)
    If i = vbYes Then
        ShellExecute 0, "open", fn, "", "", vbNormalFocus
    End If
    stg = "Resetting INI file"
proc_exit: 'Single exit point
    stg = "Exiting"
    Screen.MousePointer = 0
    Exit Sub
proc_err: 'Error trapping
    If Err = 5 And stg Like "Setting Printer*" Then
        'Your cases here
        MsgBox "Couldn't set printer to PDF995. Is PDF995 installed on this workstation?" & stg
        GoTo proc_exit
    End If
msgbox error
resume proc_exit
End Sub
Function nWritePP(ByVal Value As String, ByVal AppName As String, ByVal Section As String, ByVal Key As String) As Integer
On Error GoTo nWritePP_Err
    If UCase$(AppName) Like "*.?*" Then
        AppName = AppName & ".ini"
    End If
    nWritePP = nWritePrivateProfileString(Section, Key, Value, AppName)
    Exit Function
    MsgBox "Error: " + Error$, 0, "nWritePP"
    Resume nWritePP_Exit
End Function

Open in new window


Author Comment

ID: 21774573

Thank you, too!  I will fiddle around with this as well.  

We do have privacy measures on our site for the parents.  I have been emailing documents to the parents but they often delete them and then request them from me again (*sigh*) so we set up private document libraries for each family on our private site so they have access to the documents for six months or so.

One of these days I hope to migrate our database to the web with SQL or the like and have the parents access records & documents as needed but I have many classes to take before I'll have the skill to do that (the development costs of outside hires are often 1/3 of our annual gross.  Yikes!)    

Author Comment

ID: 21776734
Okay, here is what I did and it works like a charm!

On the open form I made a field named "FormName" and the control source:

=([Student_LName] & [Student_FName] & "QtrRptFrm" & [RptCrdStudentID] & "_" & (Format([FormDate],"yyyy-mm-dd")))

Then, I made the button and on click it runs the following code:

Private Sub Command184_Click()
DoCmd.OutputTo acOutputReport, "REPORT_GradeReport_HS", acFormatPDF, "C:\Users\MyName\Desktop\PDF Reports\" & Forms!ReportCardsF!FormName & ".pdf", False
End Sub

In the folder I specified I get the following PDF:


Is there any reason why I shouldn't keep taking this route?  I was in over my head with the modules and .ini files.  The other was was rather easy.

Oh, you have no idea how happy I am.  Puppydogbuddy, you were right on, thanks so much!  Thank you plodernator and fcfang for taking the time out to review my plight.

Well, I am bleary-eyed and exhausted.  Sweet dreams, all!


Author Closing Comment

ID: 31466622
All the extra minutes in my work day from here on out will be gratefully dedicated to you :)  Thank you sooo much for your time!

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question