Solved

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
10
2,257 Views
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.
0
Comment
Question by:eBethany
10 Comments
 
LVL 3

Expert Comment

by:fcfang
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.

http://www.mvps.org/access/reports/rpt0011.htm

Here's another link of resources for your question from other apps. Most recently I have been using PDF995 to do the PDF creation.
http://www.granite.ab.ca/access/pdffiles.htm

Hope it helps.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
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
0
 

Author Comment

by:eBethany
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.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 21773553
Bethany,
I hope it works for you....in 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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21773793
Bethany,
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.

pdb
0
 
LVL 1

Expert Comment

by:plodernator
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

        DoEvents

    Next

    

    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

            Else

                Exit For

            End If

        End If

        DoEvents

    Next

    

    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

    Else

    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

    Else

    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

    Else

        AppName = AppName & ".ini"

    End If

    

    nWritePP = nWritePrivateProfileString(Section, Key, Value, AppName)
 

nWritePP_Exit:

    Exit Function

    

nWritePP_Err:

    MsgBox "Error: " + Error$, 0, "nWritePP"

    Resume nWritePP_Exit
 

End Function

Open in new window

0
 

Author Comment

by:eBethany
ID: 21774573
plodernator:

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!)    
0
 

Author Comment

by:eBethany
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:

DoeJaneQtrRpt12345_2008-06-13.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!



0
 

Author Closing Comment

by:eBethany
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!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now