Exporting Report Query To PDF

[CharlesWhiteman]

Hi All, I am usiung the following code which is correctly running through a query and outputting PDF files. However, its not quite right.

The query behind the report contains 194 lines and raltes to call records. I want to output 194 pdf files with each pdf the report just for the related row in the query, not 194 copies of the entire report.

Private Sub Command0_Click()
Dim rs As DAO.Recordset, pdfName As String, MyFilter As String, MyFilename As String, MyPath As String
Set rs = CurrentDb.OpenRecordset("qryCalls")
rs.MoveFirst
Do Until rs.EOF
pdfName = rs("REF") & ".pdf"
'MyFilter = "Unique_Programs_Distinct.Program = '" & rs("program") & "'"
MyPath = rs("MyPath")
DoCmd.OutputTo acOutputReport, "RepCalls", acFormatPDF, MyPath & pdfName
rs.MoveNext
Loop
rs.Close
Exit_Create_PDF:
Exit Sub
End Sub

 

[JANR]

I assume that the report "RepCalls" uses the same query as it's recordsource, it so you need to filter that report BEFORE you run the OutputTo command.

I assume that you have something in that query that is unique that you can filter on, is it "Program"?? if so is it a string?

If both statements above is true then create a Public variable in a standardmodule.

Public PDFreportFilter As String

Then in the OpenEvent of your report "RepCalls" put in this code:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "[Program] = '" & PDFreportFilter & "'"
Me.FilterOn = True
PDFreportFilter = vbNullString  ' <--Reset Public variable
End Sub

The finally in your code:

Private Sub Command0_Click()
Dim rs As DAO.Recordset, pdfName As String, MyFilter As String, MyFilename As String, MyPath As String
Set rs = CurrentDb.OpenRecordset("qryCalls")
   rs.MoveFirst
     Do Until rs.EOF
       pdfName = rs("REF") & ".pdf"
       [COLOR=red]PDFreportFilter = rs!program[/COLOR]       
       MyPath = rs("MyPath")
       DoCmd.OutputTo acOutputReport, "RepCalls", acFormatPDF, MyPath & pdfName
       rs.MoveNext
     Loop
rs.Close
Exit_Create_PDF:
Exit Sub
End Sub

Hope this helps

JR