Programming MS Access report to filter on specific ID

Question

I have a recordset that I'm looping through and would like to create a report that displays information for each user ID in the recordset. I've found many posts that have helped me to write the code, but I cannot figure out why my code keeps opening the report with all user IDs instead of each user ID individually. Here's the code I'm using:


Public Function report()

Dim rs As Recordset
Dim strReportName As String
Dim fileName, pathName As String

pathName = "C:\Users\Joe\Documents"
Set rs = CurrentDb.OpenRecordset("SELECT Add_user, keyer FROM qryProductionReport;")

strReportName = "ProductionReport"

Do While Not rs.EOF
    DoCmd.OpenReport strReportName, acViewPreview, , "Add_user = " & rs!Add_user, acHidden
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, pathName & strReportName & rs!Keyer & ".PDF"
    DoCmd.Close acReport, strReportName
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Function

I made sure that my field name Add_user on the report matches the filter that I have in the DoCmd.OpenReport line. I'm using Access 2016. Thanks for the help.

Possible duplicate of [How To Output access Report with Condition](https://stackoverflow.com/questions/24302369/how-to-output-access-report-with-condition) — Erik A, Sep 23 '19 at 14:35
`DoCmd.OutputTo` doesn't care there's a filtered report open, it always prints the entire saved report. You can work around this by either binding the report to a query and adjusting that query, or using a filter with a tempvar in the report. I recommend that last approach, since it'll flexibly allow you to filter while allowing the full report to be opened and not adjusting the report or any queries. — Erik A, Sep 23 '19 at 14:37

iDevlop · Accepted Answer · 2019-09-23T15:57:56.323

One way is to modify your report to use a TempVars criteria
(e.g. [TempVars]![userId]) as explained here. You can then use it like this:

Public Function report()

Dim rs As Recordset, strReportName As String
Dim fileName as String, pathName As String

pathName = "C:\Users\Joe\Documents\"
Set rs = CurrentDb.OpenRecordset("SELECT Add_user, keyer FROM qryProductionReport;")
strReportName = "ProductionReport"

Do While Not rs.EOF
    TempVars("userId") = rs!Add_user
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, pathName & strReportName & rs!Keyer & ".PDF"
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Function

Programming MS Access report to filter on specific ID

1 Answers1