Access 2010 w/SQL backend - send multiple reports to pdf using vba without preview

Question

Scenario is an application using Access 2010 front end, SQL backend. Need exists for user to select one or more reports from form, then click button that runs through choices and runs report using strCriteria type filters depending on specific report. Most times would end up with as many as 10 different reports in 20 sets (different geographic criteria). Need to send the resulting reports to export files (PDF) for continued processing by the application (as appropriate either zips and sends files as attachments using SQL DatabaseMail system or sets task for user to send some other way).

I would like to be able to export the files without having to open the report (doCmd.OpenReport) to preview - user should not have to be involved once clicking the button. Would like to be able to pass criteria to report in command line with a WhereCondition, open report and then use doCmd.OutputTo and save as PDF (or other formats). Yes, I can pass criteria through modifying the report QueryDef first, OR create a temp table for each report that holds the data, or have fields on the selector form - have used all at one time or other. May have to, but seems there should be a way to a) OpenReport with conditions, b) keep report open without viewing OR printing, c) OutputTo a pdf (I've all sorts of functions that look at setting tables for destinations, etc.), d) close report - loop.

Any thoughts? May have to end up with temp tables (been there, done that a lot for other things), but these are NOT complicated reports - most look at a SQL view and apply the conditions. Don't wish to do through SSRS.

Thanks - Ben

O. Gungor O. Gungor · Accepted Answer · 2017-02-27T23:32:59

I think something like this will work for you, surely by altering the parameters to meet your needs. this will create the reports silently, meaning, without prompting the users. Once the reports are created, you can complete whatever else you need to do:

Sub Test()

 Const Param1 As Long = 1
 Const Param2 As String = "x"
 Const RptName As String = "r_t_test"

 DoCmd.OpenReport RptName, acViewPreview, , ("[value1] = " & Param1 & " And    [value2] = '" & Param2 & "'"), acHidden
 DoCmd.OutputTo acOutputReport, RptName, acFormatPDF, CurrentProject.Path & "\" & RptName & ".pdf", False
 DoCmd.Close acReport, RptName

End Sub

Access 2010 w/SQL backend - send multiple reports to pdf using vba without preview

1 Answers