Loop in Query to Report in PDF

[catherinebush]

I am new to this group and to this forum. I am trying to loop through a query, then save each record in the query to a pdf.

Query - ReceivedExportQuery
Report - EligibleEmployees
I want to save each record as the employee name.

I have read through most of the threads and have written the following code. It does save the report for each individual; however, it saves the report with all records. What changes to I have to do to make it save each record individually in a report?

Thanks for your help.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim mypath As String
mypath = "C:\Users\Desktop\"
Set db = CurrentDb
Set rs = db.OpenRecordset("ReceivedExportQuery")
rs.MoveFirst
Do Until rs.EOF
DoCmd.OpenReport "EligibleEmployees", acViewReport, "Name"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & rs!Name & ".pdf"
DoCmd.Close acReport, "Test"
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Sub

 

[pbaldy]

Try opening the report with a wherecondition:

http://www.baldyweb.com/wherecondition.htm

getting the value from the recordset instead of a form.

 

[catherinebush]

Pbaldy and Pat for your quick responses.

FYI - I have tried several different versions of the wherecondition and could not get it to work.

I will try the hidden control in my form and will let you know when I succeed!

 

[pbaldy]

The OutputTo method doesn't support a where argument.

That is true, but the OP's posted method should still work, once the wherecondition is added: open report filtered, output, close report. Not saying I'd do it that way, but it should work. I typically do what Pat suggested or this:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[catherinebush]

Thanks pbaldy. I must admit I am not that well versed so I am trying everything, and it takes awhile to tweek things so that they work. I am willing to try anything. I have over 2000 records that need to be stored for posterity sake in individual files in a system called NOLIJ. I really don't want to have to run them individually. I will let post what I finally get to work (being hopeful).

 

[catherinebush]

I have tried everything, I am obviously a newbie to VBA. Here is the most recent routine. Private Sub Command24_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim mypath As String
Dim strRptName As String
strRptName = "EligibleEmployees"
mypath = "C:\Users\cbush15\Desktop\"
strSQL = "Select ReceivedExportQuery.[EmployeeID] From ReceivedExportQuery;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
With rs
Do While Not rs.EOF
DoCmd.OpenReport strRptName, acViewPreview, , "[EmployeeID] = '" & ![EmployeeID] & "'"
DoCmd.OutputTo acOutputForm, strRptName, acFormatPDF, mypath & rs!EmployeeID & ".pdf"
DoCmd.Close acReport, strRptName
rs.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub


I am now getting a run-time error 2059. When I debug, it goes to the DoCmd.OutputTo ..

I took this command out and it does pull up the report with one employee's information in it... soo..... I am thinking I have some syntax wrong?? Thanks so much for your help!

 

[pbaldy]

I'm just on an iPad right now, but offhand the first argument specifies form; try changing that to the one for a report. If that doesn't work, what is the text of the error?

 

[catherinebush]

OMG - That was it! Thank you so much! I can't believe I missed that!!!

 

[pbaldy]

Happy to help!