Recordset Report

[Novice1]

I'm trying to send a report to each person in my recordset. The report is different for each person (same report but individualizesdinfo). I'm having trouble filtering the report to one record (everyone in the query shows in the report). When I don't use a recordset, I can filter the report (Filter on the report: [ReloID] = Forms!frmPCSTracker!ReloID). But the technique doesn't work when I run a recordset. What should I do differently? Thanks.



On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rscritiques As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qry91To120DaysFromPDDEmail")

With rs
If .RecordCount <> 0 Then
rs.MoveLast
iCount = rs.RecordCount

Do While Not .BOF

DoCmd.SendObject acSendReport, "rpttest", acFormatPDF, rs!SubjectEMail, rs!SupvrEMail & "; " & rs!CCF_EMail, , "PCS Orders - Assignment Actions", rs!Grade & " " & rs!LastName & ", " & vbCr & vbCr & _
"blah, blah, blah",True


.MovePrevious
Loop

End If
End With

rs.Close

MsgBox "Process complete.", vbInformation

Error_Handler_Exit:
On Error Resume Next

Set rs = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
If Err.Number <> 0 Then Err.Clear
Resume Error_Handler_Exit

 

[CJ_London]

here's a solution from 2005, should still be valid today

https://bytes.com/topic/access/answers/202750-email-filtered-report

 

[Novice1]

Can't I just put a filter on the report so when it's run it limits the record to one?

[ReloID] = Forms!frmPCSTracker!ReloID

This works great when running the report, without a recordset. When I use a query to filter the info I get all the records.

 

[Novice1]

I've narrowed the filter below to "ReloID = '" & rs!ReloID & "'" but it's still capturing all records in the query not one. Any help would be appreciated.


Dim db As DAO.Database
Dim rs As Object

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry91To120DaysFromPDDEmail")


If Not rs.EOF Then
rs.MoveFirst
Do
DoCmd.OpenReport "rptPrintOneRecord120DayAlert", acViewPreview, , "ReloID = '" & rs!ReloID & "'", acHidden
DoCmd.SendObject acSendReport, "rptPrintOneRecord120DayAlert", acFormatPDF, rs!SubjectEMail, , , "TestMessage Title", "Test message Body Text", True
DoEvents
DoCmd.Close acReport, "rptTest", acSaveNo
rs.MoveNext
Loop While Not rs.EOF
End If
Exit_SendReports_Click:
Exit Sub

 

[CJ_London]

doesn't look to me like you have followed the thread I identified