VB Filter Access Report Based On Variable

I have an Access report that I would like to loop through and print filtering by the Supervisor Name.
I tried the following code, but the report is blank.

the strSupv does match the Supervisor field on the report.

Any advice would be appreciated.

Public Function PrintReports()
Dim strSupv As String
Dim rsSUPV As New ADODB.Recordset

rsSUPV.ActiveConnection = CurrentProject.Connection
rsSUPV.Open "tblSupervisorName"

Do While Not rsSUPV.EOF
strSupv = rsSUPV("SupName")
DoCmd.OpenReport "rptEmployeeWorkList", acViewPreview, , "[Supervisor] = 'strSupv'"
End Function
Who is Participating?
IrogSintaConnect With a Mentor Commented:
It should be:
DoCmd.OpenReport "rptEmployeeWorkList", acViewPreview, , "[Supervisor] = '" & strSupv & '"

Open in new window

But why not just include tblSupervisorName in the RecordSource of your report so that it will show all the reports with separate pages for each supervisor?

ScamquistAuthor Commented:
Thank you for the assist.

I had to add a " after the last &
DoCmd.OpenReport "rptEmployeeWorkList", acViewPreview, , "[Supervisor] = '" & strSupv & "'"

I need to print the pages for the supervisor and loop because I have a couple of reports based on the supervisor, so this way I can collate them.

I appreciate the assist
ScamquistAuthor Commented:
Thank you for you help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.