Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 Report

Posted on 2010-09-09
6
Medium Priority
?
339 Views
Last Modified: 2012-06-21
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
Comment
Question by:LeLeBrown
[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
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:Remi Gelinas
ID: 33642213
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33642246
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
 

Author Comment

by:LeLeBrown
ID: 33642357
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 33642588
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33642751
or you can use this sample db
dbSavePDFAs.mdb
0
 

Author Comment

by:LeLeBrown
ID: 33642934
Capricorn1,

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

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 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