By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,281 Members | 523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,281 IT Pros & Developers. It's quick & easy.

Microsoft Access 2007 Export Report for each record within a recordset

P: 3
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



Expand|Select|Wrap|Line Numbers
  1. Private Sub AnnRptExportAll_Click()
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim ID As integer
  5.  
  6. Set db = CurrentDb()
  7. Set rs = db.OpenRecordset("BEEmployers")
  8.  
  9. If rs.RecordCount = 0 Then Exit Sub
  10.  
  11.  rs.MoveFirst
  12.   Do Until rs.EOF
  13.    ID = rs![EmployerID]
  14.    DoCmd.OpenReport StDocName, acPreview, , "[employerid]=" & ID
  15.    DoCmd.OutputTo acReport, "Annual", acFormatPDF, "G:\Research\EFR_Export\NONBE\ER_" & ID & ".pdf"
  16.    DoCmd.Close
  17.  
  18.  rs.MoveNext
  19.  Loop
  20.  
  21.  
  22. rs.Close
  23.  
  24. Set rs = Nothing
  25. Set db = Nothing
  26.  
  27. MsgBox ("Completed")
  28.  
  29. End Function
Sep 27 '12 #1
Share this Question
Share on Google+
5 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Sep 27 '12 #2

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

I did revise the line below...
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "annual", acPreview, , "[employerid]=" & ID
Sep 27 '12 #3

P: 3
I fixed it. I needed to change the end Function at the bottom to end sub.
Sep 27 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,951
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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. End Sub
line. I just noticed that your line says
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 27 '12 #5

Seth Schrock
Expert 2.5K+
P: 2,951
Splended. I just noticed that myself.
Sep 27 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.