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
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
Last Comment
DebAnn
ASKER CERTIFIED SOLUTION
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.
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_
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
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
Can you tell me what I am doing wrong??
Here is my code::
Private Sub RunProposedBillingReports_
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
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
...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
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("S
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
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("S
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
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("S
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