Avatar of DebAnn
DebAnn
Flag 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
Microsoft Access

Avatar of undefined
Last Comment
DebAnn

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

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
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
Jeffrey Coachman

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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