Output to PDF using multiple reports.
Posted on 2006-04-05
I have the following code that works fine. The basics are i have a form with a combobox. I click a command button on the form that outputs currently in SNP format various reports based on the elements in the combobox. The reports are outputted with unique names from the combobox. In addition there is an extra bit of code at the bottom that opens up another report via the button and 'virtually' prints that.
Private Sub Command10_Click()
On Error GoTo Error_Routine
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim RS As DAO.Recordset
Dim ctlComboBox As Control
Dim RS2 As DAO.Recordset ' new recordset to output separate report
Dim qdf2 As DAO.QueryDef
' the underlying recordset for the report is a query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_herb_exc_design&conagro")
qdf.Parameters(0) = Forms!frm_shedno_herb!cmb_shedherb.Value
Set RS = qdf.OpenRecordset()
Set ctlComboBox = Forms!frm_shedno_herb!cmb_shedherb
Dim i As Integer 'loop counter for items selected from combobox
'enumerate through each of the items in combo box
For i = 0 To ctlComboBox.ListCount - 1
ctlComboBox = cmb_shedherb.ItemData(i)
'preview report to apply filter
DoCmd.OpenReport "rpt_herb_exc_design&conagro_wklist", acViewPreview, , "Left([Funct# Location],6)='" & ctlComboBox.ItemData(i) & "'"
DoCmd.OutputTo acOutputReport, "rpt_herb_exc_design&conagro_wklist", acFormatSNP, "P:\Planning Engineers\weeklytest\" & ctlComboBox.ItemData(i) & ".snp", False
DoCmd.Close acReport, "rpt_herb_exc_design&conagro_wklist"
Set RS = Nothing
Set ctlComboBox = Nothing
i = 0
Set qdf2 = db.QueryDefs("qry_plan_vs_res_herbs") ' assumes no parameters coming from a combo box
Set RS2 = qdf.OpenRecordset()
DoCmd.OpenReport "rpt_plan_vs_res_herbs", acViewPreview 'output resource report via snapshot to web area
DoCmd.OutputTo acOutputReport, "rpt_plan_vs_res_herbs", acFormatSNP, "P:\Planning Engineers\weeklytest\" & "10weekherb" & ".snp", False
'close resource report
DoCmd.Close acReport, "rpt_plan_vs_res_herbs"
RS2.Close 'closing code for second recordset
Set RS2 = Nothing
MsgBox "Error# " & Err.Number & " " & Err.Description
Now i don't like the control features you get with SNP like fit to screen etc. i want to revert to PDF. Now i have found this code via a buried link on EE. I have copied everything including the notes at the top. I do have PDF995 installed on my computer. I use Access 2000
Code/wording as follows:-
VBA code that successfully converts an Access report to a PDF file.
Copy everything below the line into a module in an Access database.
To use the code on Access 2000 and earlier, it is necessary to comment out
three lines referring to the printer object and set the report to use PDF995
in the report page layout dialog.
The only way to change the printer for a report in these
previous versions is to open the report in design mode and make the change,
re-save the report, and then run it.
To create your pdf file, call the function
pdfwrite "report name","report filter(may be "")","Path to put the
A new pdf file named with the name of the report will be created.
Option Compare Database
'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lpFileName As String) As Long
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)
' Runs an Access report to PDF995 to create a pdf file from the report.
' Input parameters are the name of the report within the current database,
' the path for the output file, and an optional criteria for the report
' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is set to 0
' when pdf995 is idle. This codes uses that as a completion flag as it seems to be
' the most reliable indication that PDF995 is done writing the pdf file.
' Note: The application.printer object is not valid in Access 2000
' and earlier. In that case, set the printer in the report to pdf995
' and comment out the references herein to the application.printer
Dim syncfile As String, maxwaittime As Long
Dim iniFileName As String, tmpPrinter As Printer
Dim outputfile As String, x As Long
Dim tmpoutputfile As String, tmpAutoLaunch As String
' set the location of the PDF995.ini and the pdfsync files
iniFileName = "c:\pdf995\res\pdf995.ini"
syncfile = "c:\documents and settings\all users\application data\pdf995\res\pdfsync.ini"
' build the output file name from the path parameter and the report name
If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
outputfile = destpath & reportname & ".pdf"
' PDF995 operates asynchronously. We need to determine when it is done so we can
' continue. This is done by creating a file and having PDF995 delete it using the
' ProcessPDF parameter in its ini file which runs a command when it is complete.
' save current settings from the PDF995.ini file
tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)
' remove previous pdf if it exists
On Error Resume Next
On Error GoTo Cleanup
' setup new values in PDF995.ini
x = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)
' change the default printer to PDF995
' if running on Access 2000 or earlier, comment out the next two lines
Set tmpPrinter = Application.Printer
Application.Printer = Application.Printers("PDF995")
'print the report
DoCmd.OpenReport reportname, acViewNormal, , strcriteria
' cleanup delay to allow PDF995 to finish up. When flagfile is nolonger present, PDF995 is done.
maxwaittime = 300000 'If pdf995 isn't done in 5 min, quit anyway
Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
maxwaittime = maxwaittime - 10000
' restore the original default printer and the PDF995.ini settings
x = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
On Error Resume Next
' if running on Access 2000 or earlier, comment out the next line
Application.Printer = tmpPrinter
Function ReadINIfile(sSection As String, sEntry As String, sFilename As String) As String
Dim x As Long
Dim sDefault As String
Dim sRetBuf As String, iLenBuf As Integer
Dim sValue As String
'Explanation of arguments:
'sSection: ini file section (always between brackets)
'sEntry : word on left side of "=" sign
'sDefault$: value returned if function is unsuccessful
'sRetBuf$ : the value you're looking for will be copied to this buffer string
'iLenBuf% : Length in characters of the buffer string
'sFileName: Path to the ini file
sDefault$ = ""
sRetBuf$ = String$(256, 0) '256 null characters
iLenBuf% = Len(sRetBuf$)
x = GetPrivateProfileString(sSection, sEntry, _
sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, x)
Now in theory i have everything to go into PDF production. One problem, I don't know how to!!!!!!
Looking at the PDF995 code it seems to focus on one report rather than the variables i am passing. I am hoping that the PDF code can be streamlined into the existing code.
regards and thanks in advance, vipa