I'm trying to send reports via email using VBA, but I can't figure out how to filter the recordsource so that only a single record goes to each person. Here's my code:
Dim strSQL As String
On Error Resume Next
Dim DB As Database
Dim RSValues As DAO.Recordset
Set DB = CurrentDb()
strSQL = "SELECT Email,pk_InitiativeID FROM tbl_Initiatives WHERE NOT EXISTS " & _
"(SELECT tbl_EventLog.DateOfEvent " & _
"FROM tbl_EventLog " & _
"WHERE tbl_EventLog.fk_InitiativeID=tbl_Initiatives.pk_In itiativeID " & _
"AND tbl_EventLog.fk_EventID=1 )"
Debug.Print strSQL
Set RSValues = DB.OpenRecordset(strSQL)
RSValues.MoveLast
RSValues.MoveFirst
Do While Not RSValues.EOF
Set rpt_Report.RecordSource = "SELECT * FROM tbl_Initiatives WHERE pk_InitiativeID=" & pk_InitiativeID
DoCmd.SendObject acSendReport, "rpt_Report", acFormatPDF, Email, , , "Status Report for ", "This is a test", 0
RSValues.MoveNext
Loop
It sends the report to the right people so the loop is working fine, but it sends the entire report rather than just the report for the associated person.
thanks!