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,300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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