Open report in preview, save file as PDF on hard drive with custom name (1 Viewer)

B

brianborn

Registered User.
Local time
Today, 01:33
Joined
Jul 14, 2011
Messages
38
I created a database on my personal laptop that has Access 2010. There are several reports that are generated via a parameter query and with the use of macros, they are then emailed in PDF format when I click a button on the main form I use. However, the version of Access I have at work is 2003 so these buttons no longer work. I created new buttons to preview the report so that I can save as a PDF and email to those who require them. There are a lot more steps doing it this way and I know there has to be a better way.

Below is the VBA code generated by Access.

Code: 
Private Sub ReportCS_Click()
On Error GoTo Err_ReportCS_Click

    Dim stDocName As String

    stDocName = "rpt_LTL Shipments for CS"
    DoCmd.OpenReport stDocName, acPreview

Exit_ReportCS_Click:
    Exit Sub

Err_ReportCS_Click:
    MsgBox Err.Description
    Resume Exit_ReportCS_Click
    
End Sub
This is a shipping report and when I click the button, I enter the date in as "1/27" and the report shows the shipping for that day. Now I need to right click and print to a PDF format and the name always default to the name of the report in Access. What I would like it to do is always save in the same folder on my hard drive and rename it to "CS - Shipping Report 2012.01.27.pdf" whereas the date would change to the one entered in the parameter.

Is this something that would be easy to code? Or can anyone suggest other options?

Hopefully I explained this well enough.
 
J

josephff

Registered User.
Local time
Yesterday, 23:33
Joined
Oct 25, 2011
Messages
72
This is the code i use to save a pdf to desktop. I save mine to here then open up outlook, send email in another code then delete the file back off the desktop.

first on my button event
Code: 
DoCmd.OpenReport "Overview", acViewPreview
    PrintToPDF ("Overview")
 
    Dim year As String
    year = Nz(DLookup("CurrentYear", "CurrentVariables"))
 
    Dim olLook As Object                     'Start MS Outlook
    Dim olNewEmail As Object                 'New email in Outlook
    Dim strEmailSubject As String
    Dim strEmailText As String
 
    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.createitem(0)
    strEmailSubject = year & Space(1) & "Inventory Overview Report PDF "
    strEmailText = "Please see attached for Inventory Product Group Overview Report"
   With olNewEmail   'Attach template
      .body = strEmailText
      .subject = strEmailSubject
      .attachments.Add ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\" & year & "-" & "Overview.pdf")
      .display
   End With
 
   Kill ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\" & year & "-" & "Overview.pdf")
   DoCmd.Close acReport, "Overview", acSaveYes

i call the function below using PrintToPDF ("Overview"), Overview being the name of the report.

Code: 
Function PrintToPDF(SrcFile As String)
 
On Error GoTo PrintToPDF_Err
 
Dim DestPath As String
Dim DestFile As String
Dim ShowPdf As Boolean
Dim year As String
year = Nz(DLookup("CurrentYear", "CurrentVariables"))
 
DestPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
 
DestFile = year & "-" & SrcFile
ShowPdf = False
 
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint
 
PrintToPDF_Exit:
    Exit Function
PrintToPDF_Err:
    MsgBox Error$
    Resume PrintToPDF_Exit
End Function

hope this helps
 
B

brianborn

Registered User.
Local time
Today, 01:33
Joined
Jul 14, 2011
Messages
38
Thanks Joseph. When I go back to work on Monday, I will try it out and let you know how it work. I really appreciate it.
 
B

brianborn

Registered User.
Local time
Today, 01:33
Joined
Jul 14, 2011
Messages
38
josephff said:
This is the code i use to save a pdf to desktop. I save mine to here then open up outlook, send email in another code then delete the file back off the desktop.

first on my button event
Code: 
DoCmd.OpenReport "Overview", acViewPreview
    PrintToPDF ("Overview")
 
    {SNIP}...
i call the function below using PrintToPDF ("Overview"), Overview being the name of the report.

Code: 
Function PrintToPDF(SrcFile As String)
 
    {SNIP}...
 
[B]DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint[/B]
 
    {SNIP}...
hope this helps
Click to expand...

Since I now work part time so I can finish school, it too me a few days to get to try this out. When I click the command button, it starts to go through the steps, but then I get a Compile Error on the bold command about. The error is "Wrong number of arguments or invalid property assignment". Since I am just learning about VBA, I'm not sure what to look for. I clicked help when the dialog box opened and googled the error message and I'm not sure what to do now.

Any suggestions?

Brian
 
J

josephff

Registered User.
Local time
Yesterday, 23:33
Joined
Oct 25, 2011
Messages
72
copy your code into here so i can see it.


Any time you see a sub or a function with parenthesis at the end such as in this example

Function PrintToPDF(SrcFile As String)

the stuff in parenthesis is called an argument, meaning you have to pass something to it, because the code is expecting it.

so when i would call it from my command button code

PrintToPdf("Overview") the Overview is my argument. Also please note that the argument in this case is a string...if you were using a number or date or whatever else you would have to change it to

Function PrintToPDF(SrcFile As Integer) or date etc.

post the implementation of the code into your project here and ill look at it

or you can upload a sample database and i can look at it that way...sometimes thats easier to play with
 
B

brianborn

Registered User.
Local time
Today, 01:33
Joined
Jul 14, 2011
Messages
38
josephff said:
copy your code into here so i can see it.
Click to expand...

The only part that I changed is in bold. I tried to figure it out myself, but wasn't sure what it could be.


Code: 
Private Sub ReportCS_Click()
DoCmd.OpenReport "[B]rpt_LTL Shipments for CS[/B]", acViewPreview
    PrintToPDF ("[B]rpt_LTL Shipments for CS[/B]")
 
    Dim year As String
    year = Nz(DLookup("CurrentYear", "CurrentVariables"))
 
    Dim olLook As Object                     'Start MS Outlook
    Dim olNewEmail As Object                 'New email in Outlook
    Dim strEmailSubject As String
    Dim strEmailText As String
 
    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.createitem(0)
    strEmailSubject = year & Space(1) & "Inventory Overview Report PDF "
    strEmailText = "Please see attached for Inventory Product Group Overview Report"
   With olNewEmail   'Attach template
      .body = strEmailText
      .subject = strEmailSubject
      .attachments.Add ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\" & year & "-" & "Overview.pdf")
      .display
   End With
 
   Kill ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\" & year & "-" & "Overview.pdf")
   DoCmd.Close acReport, "Overview", acSaveYes

End Sub
Code: 
Function PrintToPDF(SrcFile As String)
 
On Error GoTo PrintToPDF_Err
 
Dim DestPath As String
Dim DestFile As String
Dim ShowPdf As Boolean
Dim year As String
year = Nz(DLookup("CurrentYear", "CurrentVariables"))
 
DestPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
 
DestFile = year & "-" & SrcFile
ShowPdf = False
 
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint
 
PrintToPDF_Exit:
    Exit Function
PrintToPDF_Err:
    MsgBox Error$
    Resume PrintToPDF_Exit
End Function
 
J

josephff

Registered User.
Local time
Yesterday, 23:33
Joined
Oct 25, 2011
Messages
72
ok try this

Code: 
Private Sub Command11_Click()
    DoCmd.OpenReport "rpt_LTL Shipments for CS", acViewPreview
    PrintToPDF ("rpt_LTL Shipments for CS")
 
    'Dim olLook As Object                     'Start MS Outlook
    'Dim olNewEmail As Object                 'New email in Outlook
    'Dim strEmailSubject As String
    'Dim strEmailText As String
 
    'Set olLook = CreateObject("Outlook.Application")
    'Set olNewEmail = olLook.createitem(0)
    'strEmailSubject = "Inventory Overview Report PDF "
    'strEmailText = "Please see attached for Inventory Product Group Overview Report"
   'With olNewEmail   'Attach template
   '   .body = strEmailText
   '   .subject = strEmailSubject
   '   .attachments.Add ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\Overview.pdf")
   '   .display
   'End With
 
   'Kill ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\Overview.pdf")
   DoCmd.Close acReport, "Overview", acSaveYes
End Sub

Code: 
Function PrintToPDF(SrcFile As String)
 
On Error GoTo PrintToPDF_Err
 
Dim DestPath As String
Dim DestFile As String
Dim ShowPdf As Boolean
Dim year As String
 
DestPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
 
DestFile = SrcFile
ShowPdf = False
 
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint
 
PrintToPDF_Exit:
    Exit Function
PrintToPDF_Err:
    MsgBox Error$
    Resume PrintToPDF_Exit
End Function

what i have done is removed all reference to the "year" cause that was a lookup based on my tables in my program. I have also commented out all the email portion. So when you run the button it should save your report to the desktop in a pdf. I tested this in a blank database so i know it works.
 
J

josephff

Registered User.
Local time
Yesterday, 23:33
Joined
Oct 25, 2011
Messages
72
also make sure your report name is correct, if your report is named "rpt_LTL Shipments for CS" then you should be fine but if your report is named "LTL Shipments for CS" then you need to make it that. Not what it shows in the VBA editor but what it shows on the designer view.
 
J

josephff

Registered User.
Local time
Yesterday, 23:33
Joined
Oct 25, 2011
Messages
72
change

Code: 
DoCmd.Close acReport, "Overview", acSaveYes

to

Code: 
DoCmd.Close acReport, "rpt_LTL Shipments for CS", acSaveYes
 
B

brianborn

Registered User.
Local time
Today, 01:33
Joined
Jul 14, 2011
Messages
38
I tried all that you suggested and I still get the same error. I will put the database on my USB drive and sometime tonight when I get home, I will save a copy of the database without all the database and upload a copy.

Thanks for the help as I really appreciate it.

BTW... I am going to school for programming, but haven't made it as far as this type of programming yet. I just started my 2nd semester of C++ and also taking PHP, ASP.NET, JavaScript and 2 database classes which we haven't covered a lot in just yet. So I understand the lingo like parameter and passing values, just not a lot of experience with VBA.
 
B

brianborn

Registered User.
Local time
Today, 01:33
Joined
Jul 14, 2011
Messages
38
I updated the database to version 2007, saved as a template, then deleted any private info from the forms and now there are errors in the database. I'm sure the problem is something minor that I'm doing wrong and I will figure out what it is over the next week as I find time.

Brian
 
D

diS

Registered User.
Local time
Today, 06:33
Joined
Jan 16, 2015
Messages
17
Given method works great, but what if we want to just open the report (in pdf) rather than save it on disk?

Is there a way to accomplish this?
 
You must log in or register to reply here.

Similar threads

C
New error when saving PDF
Replies
6
Views
629
cricketbird
C
MajP
Load Local PDF into Webbrowser
Replies
7
Views
388
Gasman
Gasman
1
Help with creating a button to export PDF file
2
Replies
33
Views
1,942
Gasman
Gasman
M
VBA output report to PDF and email
Replies
18
Views
896
Pat Hartman
P
P
Error code solution
Replies
14
Views
602
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom