Pass Filter to Sub Report

[AiArch]

I have a form where you select an employee and then there is a multi select list box where you select the projects you want to print for that employee. I can get the main report to open with all the employee information but the problem I am having is I am not able to pass the filter (the projects that were selected) to the subreport (the subreport contains all the project information).

Here is the code I am using to save the filter as a string and open the main report(rptCustomResume)

Public strFilter as string

Dim varItem As Variant
For Each varItem In Me!lstProjects.ItemsSelected
strFilter = strFilter & "[EPRproid] = '" & _
Me![lstProjects].ItemData(varItem) & "' OR "
Next ' continue loop

If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
Else
MsgBox "You did not select any project(s)."
lstProjects.SetFocus
Exit Sub
End If

DoCmd.OpenReport "rptCustomResume", acViewPreview

I need strFilter to filter a subreport called rptCustomResume-ProjectsSub.

Thanks in advance for your help

 

[PHV]

You may consider using the OpenArgs parameter of the DoCmd.OpenReport method and the OpenArgs property of the Report object.
Another note: using OR conditionals is often messy, noticely when mixed with AND operator.
For Each varItem In Me!lstProjects.ItemsSelected
strFilter = strFilter & ",'" & _
Me![lstProjects].ItemData(varItem) & "'"
Next ' continue loop

If strFilter <> "" Then
strFilter = "[EPRproid] In (" & Mid(strFilter, 2) & ")"
Else
MsgBox "You did not select any project(s)."
lstProjects.SetFocus
Exit Sub
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AiArch]

Do you mean using a parameter within the query itself? ([Enter projects] or someting along those lines).

I was using DoCmd.OpenReport "name" , acPreview , , strFilter

Is there a way to filter the sub report with the projects selected form the list box.

When main report opens it ask you to enter the name of the employee also... dont know if that matters

Thanks for the help... I am still new at this

 

[PHV]

Have you checked your object browser (F2) and your help engine (F1) for the 2 OpenArgs I suggested you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AiArch]

I am messing around with the OpenArgs and i am still having problems. Do you have an example of what i would type for the OpenArgs. I have not used this before so i am still trying to learn as i go. Thanks again

 

[AiArch]

I tried things like this.

DoCmd.OpenReport "rptCustomResume", acPreview, , , , OpenArgs:=strFilter

this is from the click event of the button i have to preview the report.

i know its not right but didn't know if it would help to see what i am doing wrong

 

[PHV]

Now the Open event procedure of rptCustomResume may grab the filter for the subreport.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AiArch]

Do I need to type code into the Open event of rptCustomResume to pass the filter to the subreport? If so how would I do that?

 

[AiArch]

Do I need something like this in open function of the sub report

Me.Filter = Reports!rptCustomResume.OpenArgs