Run same report for multiple users 1

[darude]

Hi Everyone,
I have a report that is sent to 23 people. I would like to create something that can feed each persons name into the same query. Currently i have 23 of the same queries for 23 of the same reports. Any help would be fantastic!!! Thank you.

 

[hymn]

Have you thought of mail merge

Hope this helps
Hymn

 

[dhookom]

Can you include all 23 people in the same report and page break between them? What do you mean by "sent"? Is this printed and mailed, emailed, automatically emailed,...?

Do you feel comfortable with applying a coding solution?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[darude]

Hi Duane,
It is currently being automatically emailed. I can't include all 23 people in the same report because they are being emailed and they are confidential. I am comfortable with a coding solution and i know i have to have the program iterate through the 23 names i'm just not sure where to start. Thank you.

 

[dhookom]

I would create a single report that includes all people. Assume there is a PeopleID primary key that is included in the report. Create a recordset that contains the unique PeopleIDs

Dim strSQL as String
Dim strWhere as String
Dim db as DAO.Database
Dim rs as DAO.Recordset
strSQL = "SELECT PeopleID FROM tblPeople..."
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
With rs
Do Until .eof
strWhere = "[PeopleID]=" & !PeopleID
'your code goes here to send the report
DoCmd.OpenReport "rpt...", , , strWhere
.movenext
Loop
.close
End With
Set rs = nothing
Set db = Nothing


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rookie52]

Dhookom??????

Hi Darude, I don't mean to hijack your thread, but Dhookom alluded to a solution that would fix my problem.

My problem would be solved by using the same report and inserting page breaks between the sections of the report.

At the momment, my code generates all the info, and prints it to the debug window, but only the information from 1 loop is displayed in a report. I call the report from inside the loop, but it only opens once.

My question is do I use the DoCmd.openreport "result_report", acViewPreview code for each iteration, or only once, then append to the report somehow, and how do I insert a page break?

If I should be appending, please advise how this can be done.

Thanks!

 

[dhookom]

rookie52,
It is very difficult to open the same report multiple times in preview. I would not even attempt it. The original question was sending the report (however it was coded) rather than previewing.

You haven't provided much justification for not displaying a standard report that is bound to a query or sql statement.

I would suggest you start a new thread so more people will view your question.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[darude]

Thanks Duane, that was just what I was looking for.