Loop in Query to Report in PDF (1 Viewer)

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

Try opening the report with a wherecondition:

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

getting the value from the recordset instead of a form.

The OutputTo method doesn't support a where argument. I handle this by adding a hidden control to my form. Then the RecordSource query of the report uses the hidden control on the form for selection criteria. You're almost there. In the code above as you loop through the recordset, just copy the PK to the hidden form field and change the report's query to get the criteria from the form.

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!

Pat Hartman said:

The OutputTo method doesn't support a where argument.

Click to expand...

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

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

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!

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?

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

Happy to help!