Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Access 2007 Report

I have a Access 2007 report that I want to convert to PDF and then save. I want the save as dialog box to come up so the user can choose a folder to save it to. I am using a macro (specs below) to create the PDF and tried using the msofiledialogsaveas to open the dialog box. the trouble is, I can't get them to work together.

Object Type:  Report
Object Name:  rptPendingDateChanges
Output Format: PDF Format(*.pdf)
output file:  had a path and file name here before
auto start: NO
Template File:  
Encoding:  
Output Quality: Print
0
LeLeBrown
Asked:
LeLeBrown
  • 3
  • 2
1 Solution
 
Remi GelinasDeveloppeurCommented:
Hello,

If you are using the "Microsoft Office Save as PDF Plug-in", you can setup the "Adobe PDF" printer to ask for file prompt before printing the file  (right click your adobe printer in your printer list and show properties, go in the general tab).
0
 
Rey Obrero (Capricorn1)Commented:
the macro can not take variables. To make this work using the msofiledialogsaveas, you have to create a function in vba.

post the codes you are using with the msofiledialogsaveas.

you can do this without using a macro if you want.
0
 
LeLeBrownAuthor Commented:
Capricorn1,

with msofiledialogsaveas i just have that statement right now. it has been a while since I have created a file in Access. All I have right now is below.

DoCmd.SetWarnings False
 strStatus = SysCmd(acSysCmdSetStatus, "Creating report, please wait ...")
 DoCmd.RunMacro "ReportToPDF"
     strStatus = SysCmd(acSysCmdClearStatus)


Set dlgOpen = Application.FileDialog(msoFileDialogSaveAs)
0
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'.

 
Rey Obrero (Capricorn1)Commented:
create a command button in a form and name it cmdSavePDF

in the click event of the button place the code like this

you have to add to your references Microsoft office xx.x object library

Private Sub cmdSavePDF_Click()
On Error GoTo SavePDFerr
Dim dlgOpen As Office.FileDialog, pdfFile As String
Set dlgOpen = Application.FileDialog(msoFileDialogSaveAs)
With dlgOpen
    .AllowMultiSelect = False
    .Title = "Save PDF file"
    .Show
    pdfFile = .SelectedItems(1)
End With
If Len(pdfFile & "") > 0 Then
DoCmd.OutputTo acOutputReport, "rptPendingDateChange", acFormatPDF, pdfFile, , , , acExportQualityPrint

End If
Exit_SavePDF:
    Exit Sub

SavePDFerr:
    If Err.Number = 5 Then
        Err.Clear
        Else
        MsgBox Err.Description
    End If
        Resume Exit_SavePDF

End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
or you can use this sample db
dbSavePDFAs.mdb
0
 
LeLeBrownAuthor Commented:
Capricorn1,

Thanks!!  This worked great!
0
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

Featured Post

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

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now