Link to home
Start Free TrialLog in
Avatar of DebAnn
DebAnnFlag for United States of America

asked on

Print report for multiple criteria by iterating through IDs - MS Access

I have created a report that I would like to print by pushing a button.  I can do that, but I want it to print to pdf the report for each advisor.  I'm not sure how to get it to iterate through and print the report for each advisor.  

I have a list of advisors in a table, OR could it run through the query and print for each advisor as the number changes? Every advisor will not have data every time, however, if they do have data I want to print the report to pdf so I can email it to them.

I'm relatively new at this and I'm totally stumped - can anyone help me out??

Thanks so much,
Deb
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ooops, you said "Print", not email...

So do you mean "Create" a pdf?
... or do you actually want to "print" (hard-copy)  the report?
(or both?)
if so, you can just print the report, as it will be exactly the same as printing the PDF.

Here is the code to do both.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ManID FROM tblMan")
rst.MoveFirst
Do Until rst.EOF
    'Actually Print the report
    DoCmd.OpenReport "rptSales", , , "ManID=" & rst!ManID
    'Save the report to a PDF
    DoCmd.OpenReport "rptSales", acViewPreview, , "ManID=" & rst!ManID, acHidden
    DoCmd.OutputTo acOutputReport, "rptSales", acFormatPDF, "C:\YourFolder\" & "Manager" & rst!ManID & "-Report.pdf"
    DoCmd.Close acReport, "rptSales"
    rst.MoveNext
Loop
    MsgBox "done"
   
rst.Close
Set rst = Nothing



...I am sure you will be able to modify this to work in your environment.

;-)

JeffCoachman
Avatar of DebAnn

ASKER

I tried the following and I get a popup box requesting the advisor number for the (OpenReport) line and then a syntax error on the Send Object line.
Can you tell me what I am doing wrong??
Here is my code::


Private Sub RunProposedBillingReports_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Payor ID],[AdvisorsEmail] FROM Advisors")
rst.MoveFirst
Do Until rst.EOF
    DoCmd.OpenReport "Proposed Billing Accounts", acViewPreview, , "Advisor_Number=" & rst![Payor ID], acHidden
    DoCmd.SendObject acSendReport, "Proposed Billing Accounts", acFormatPDF, rst![AdvisorsEmail], , , "Some Subject", "Some Message text", False
    DoCmd.Close acReport, " Proposed Billing Accounts "
    rst.MoveNext
Loop
    MsgBox "done"
   
rst.Close
Set rst = Nothing

End Sub
If you are specifying "Advisor_Number", then why is the criteria: "rst![Payor ID]" ?
...in other words, if these are the fields in the Advisors table:
[Payor ID],[AdvisorsEmail]

...then Payor ID (not sure why this is not "AdvisorID"...?)  should be in the report as well

DoCmd.OpenReport "Proposed Billing Accounts", acViewPreview, , "[Payor ID]=" & rst![Payor ID], acHidden

Please post a sample database of what you have so far
Avatar of DebAnn

ASKER

Sorry - was pulled off this project, but now am back on.
I am getting an error "access run-time error 3061 too few parameters. expected 2"

Here is what I have coded - any idea what I'm doing wrong?  The query uses a start and end date field from the calling form.  Is this the issue perhaps??
Thanks for the help.....

Private Sub Command65_Click()
'Print to pdf 1099 Statements for all advisors with activity

Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset("SELECT [Rep] FROM [1099 Details Query]")

rst.MoveFirst

Do Until rst.EOF
    'View and Save the report as a pdf
    DoCmd.OpenReport "[1099 Details Report]", acViewPreview, , "Rep=" & rst!Rep, acHidden
    DoCmd.OutputTo acOutputReport, "[1099 Details Report]", acFormatPDF, "J:\1099 Reports\" & rst!Rep & "1099 Details 2012.pdf"
    DoCmd.Close acReport, "1099 Details Report"
    rst.MoveNext
Loop
    MsgBox "Done"
   
rst.Close
Set rst = Nothing

End Sub