I have a database containing a 2 record ids. The record ids are used in the select statement for the report, then emailed to a recipient. The issue I have is that while the record ids change the selection doesn't, so I get the same report with the same selection emailed for the number of uniqe record ids. For instance, I have a record Id of 380, 433, the code sends out 2 emails but with only the 380 record id selected. The code I am using is below. Any help would be appreciated. BTW - record id in the above description is the same as respondent id in the code below. Any help would be appreciated.
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Dim rst As DAO.Recordset2
Set db = CurrentDb
Set rs = db.OpenRecordset("tmpPlay", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
Form_frmLaunchEmail!Text1 = rs("RespondentID")
DoCmd.OpenReport "Associate Supervisor Survey Comparison Reqd Diff", acViewPreview, , "[RespondentID] =" & rs![RespondentID]
DoCmd.SendObject acSendReport, "Associate Supervisor Survey Comparison Reqd Diff", "PDFFormat(*.pdf)", "rsfirebaugh@sbcglobal.net", , , "Associate Supervisor Comparison Report", "Here is the report", False
DoCmd.Close acReport, "Associate Supervisor Survey Comparison Req Diff", acSaveNo
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
Exit Sub
End Sub
by: dqmqPosted on 2009-11-03 at 08:21:12ID: 25730535
Your difficulty is conveying the Respondent ID to the report. You do it using a filter in the report preview, but you do not do it at all in the emailed report. Frankly, I'm suprised the emailed report doesn't show all receipients.
One way to accomplish that is to use the reports recordsource, which is either a table, query, or select statement. If it's a table, then change it to a select statement like this:
select * from yourtablename where RespondentID=[Form_frmLaun
If it is a query or a select statement, then add the above where condition to limit the report to the respondentID that is visible (momentarily) on the form.
If above works, then remove the RespondentID filter from the OpenReport statement.
----------------
BTW, in code you alternate previewing a report and sending the report. However, you have no synchronization. In other words, all the preview windows will open and all the reports will get emailed before you have the opportunity to preview the first one.