Microsoft Access 2007 Export Report for each record within a recordset

[jbrown8253]

Microsoft Access 2007 Export Report for each record within a recordset

I am attempting to click a button on a form that will export a report that is unique to each record. I created a query that contains the Employer ID numbers that the report should be run for. I am unable to make it work. The report currently runs fine when I enter in the EmployerID number one at a time.

Below are the fields being used:

(BEEmployers) Qry with records that need reports run
(EmployerID) Column within query that contains record ID data that needs passed to report
(Annual) Name of report that needs printed for each record

Private Sub AnnRptExportAll_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ID As integer
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("BEEmployers")
 
If rs.RecordCount = 0 Then Exit Sub
 
 rs.MoveFirst
  Do Until rs.EOF
   ID = rs![EmployerID]
   DoCmd.OpenReport StDocName, acPreview, , "[employerid]=" & ID
   DoCmd.OutputTo acReport, "Annual", acFormatPDF, "G:\Research\EFR_Export\NONBE\ER_" & ID & ".pdf"
   DoCmd.Close
 
 rs.MoveNext
 Loop
 
 
rs.Close
 
Set rs = Nothing
Set db = Nothing
 
MsgBox ("Completed")
 
End Function

[Seth Schrock]

When you say you are unable to get it to work, what do you mean? Are you getting error messages (if so, what are they), or does it run, but not the way that you want it to (if so, what does happen)?

Also, when you post code, please use the <code/> button.

[jbrown8253]

Currently the code does not run and there are no errors.

I did revise the line below...

DoCmd.OpenReport "annual", acPreview, , "[employerid]=" & ID

[jbrown8253]

I fixed it. I needed to change the end Function at the bottom to end sub.

[Seth Schrock]

Please use the <code/> format when posting code.

If there are no error messages, then the code is running (unless it got disconnected from the button AnnRptExportAll's On_Click event) , but it could be exiting prior to when it is supposed to. Click on the gray area to the left of the

Set db = CurrentDb()

line. A red dot should appear. Then click your button to trigger the code. The code will stop at the line with the red dot and highlight that line in yellow. Now press F8 to go to the next line. Keep pressing F8 and watch to see when it goes to the

End Sub

line. I just noticed that your line says

End Function

That needs to be Sub, not Function. Going through the code in this way will either produce the error or tell us what line of code got executed last.

[Seth Schrock]

Splended. I just noticed that myself.