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
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