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,251 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

11 Experts available now in Live!

Get 1:1 Help Now