1 Report Multiple PDF's

[mborg]

Need to make report books and save each as a pdf. Each book is dependent on a certain Field (ID). Where the ID is the same, need to have a book. There are about 5000 pages of data, and should be about 150 books. Instead of going into a query 150 times, changing the criteria and rerunning the report I want to be able to just run a vba script that loops through and saves off a book of the report... Here is what I have so far. Please advise. I am getting a compile error, plus I am not even sure if the code is proper:


Private Sub Report_Open(Cancel As Integer)

Dim sql As String
Dim db As Database
Dim rs As Recordset
sql = "SELECT [Distribution] FROM [qry1]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Do Until rs.EOF
DoCmd.OpenReport "Report", acViewPreview,,"ID = " &
rs!ID
DoCmd.OutputTo
acOutputReport , "Report", [acFormatPDF], "C:\" & ID & ".pdf"
DoCmd.Close acReport, "Report"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[HiTechCoach]

Need to make report books and save each as a pdf. Each book is dependent on a certain Field (ID). Where the ID is the same, need to have a book. There are about 5000 pages of data, and should be about 150 books. Instead of going into a query 150 times, changing the criteria and rerunning the report I want to be able to just run a vba script that loops through and saves off a book of the report... Here is what I have so far. Please advise. I am getting a compile error, plus I am not even sure if the code is proper:


Private Sub Report_Open(Cancel As Integer)

Dim sql As String
Dim db As Database
Dim rs As Recordset
sql = "SELECT [Distribution] FROM [qry1]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Do Until rs.EOF
DoCmd.OpenReport "Report", acViewPreview,,"ID = " &
rs!ID
DoCmd.OutputTo
acOutputReport , "Report", [acFormatPDF], "C:\" & ID & ".pdf"
DoCmd.Close acReport, "Report"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I assume you are sing Access 2007 or 2010. Is that correct?

First you need to put the coen in a differnt event thatn a report's On Open event,( Report_Open)

I would recommend using the On Click event of a Command Button.

I have created an example for Access 2007/2010 that shows I how I like to handle this. See: Batch Printing and Save As PDF