Exporting Access Report to PDF using VBA (Criteria)

[mesh1o]

Hi all,

I've got a report I want to export to PDF but not sure how to go about it. I know it's done using DoCmd.OutputTo acOutputReport, "rptNominalRollCC", acFormatPDF,

The report itself is based on a query that I've created and whilst the report works I'm struggling to get the loop to work. When opening the report, it will ask me to enter a cost centre and once entered will open the report detailing the cost centres details. What I'm trying to achieve is a loop to process a list of cost centres (store in table : "hhplCC") and automate the export process to save the PDFs to c:\nroll\*costcentre*.pdf

Any ideas?

Thanks

 

[Cronk]

I presume from your response in

https://access-programmers.co.uk/forums/showthread.php?p=1522165#post1522165

that your loop is working fine. You are probably finding that you cannot pass a filter with the docmd.outputTo command.

You could rewrite your underlying data query to include a filter based on the contents of a text box in a form and insert the value of the cost centre in the text box in the loop.

Something like

do while not rst.eof
  Forms!yourForm!txtBox= rst!costCenter
  docmd.outputTo.....
  rst.moveNext
loop

loop

 

[mesh1o]

Hi

thank you for your response. this is my script

Private Sub Create_PDF_Click()

Dim myPath As String
Dim strReportName As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("NominalRoll_PDF")
Set rs1 = CurrentDb.OpenRecordset("DivCNR")
myPath = "\\Accounts\COMMON\Nominal Roll"
strReportName = "RPTNominalRoll"
Do While Not rs.EOF
    DoCmd.OpenReport strReportName, acViewPreview, , "[Cost Centre] = '" & rs1![Cost Centre] & "'", acHidden
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myPath & "Division C" & "-" & rs1![Cost Centre] & ".PDF"
   DoCmd.Close acReport, strReportName
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

However, the cost centre loop doesn't work just stops at the first record in table DivCNR rather then cycling through all 77 records and pulling off 77 PDFs from the potential 200

Not sure if that makes sense :/

thanks

 

[pbaldy]

See the problem?

looping on:
Do While Not rs.EOF

using
rs1![Cost Centre]