Report filtered from combo boxes only showing first record

[ScottishFi]

Hi there



I've got a reports form which filters a subform from three combo boxes, now working brilliantly thanks to help on a previous post. I was also given the code for a command button to open a report on the same filters. All good, except that the report only ever shows the first record from the filtered selection where I need it to show all relevant records. The query must be correct as it's the same source for the subform which does show more records. Is there something obvious I'm missing on the report setup? I can't see any groupings on the report but I am completely new to setting them up. Here's the code I was given for filtering:

Option Compare Database

Option Explicit

Sub cboDep_AfterUpdate()
FilterSub
End Sub

Sub cboBud_AfterUpdate()
FilterSub
End Sub

Sub cboSup_AfterUpdate()
FilterSub
End Sub

Private Sub FormHeader_Click()

End Sub

Private Sub Print_selection_Click()
DoCmd.OpenReport "Reports", acViewReport, , "[Purchase Order ID]=" & Me.ctrpurchases![Purchase Order ID]
End Sub

Sub FilterSub()
Dim sWhere As String
sWhere = "1=1"
If Not IsNull(cbodep) Then sWhere = sWhere & " and [Department Code]=" & cbodep
If Not IsNull(cbobud) Then sWhere = sWhere & " and [Finance Code]=" & cbobud
If Not IsNull(cbosup) Then sWhere = sWhere & " and [Supplier ID]=" & cbosup
If sWhere = "1=1" Then
Me.ctrpurchases.Form.FilterOn = False
Else
Me.ctrpurchases.Form.Filter = sWhere
Me.ctrpurchases.Form.FilterOn = True
End If
End Sub

I've started a new post as I marked the old one as Solved, if that was the wrong thing to do please let me know. All suggestions gratefully received, I'm afraid my coding is limited to direct copying.

Thanks!

[aytee111]

The report is being given a purchase order id, whereas the form filter is made up of other fields. To use the same filter for the report, change the "where" string in the openreport line to Me.Filter

[ranman256]

yes, my code only filters the form.
If you want it to filter the report, then:

DoCmd.OpenReport "Reports", acViewReport, , sWhere

[ScottishFi]

Thanks guys, although when I change it to sWhere I get 'variable not defined' and if I put "sWhere" I get a parameter box for sWhere? I didn't change any of the rest of the code, just updated the Print sub to:

Private Sub Print_selection_Click()
DoCmd.OpenReport "Reports", acViewReport, , sWhere
End Sub

Is that right? Does it matter where it sits in the rest of the code?

And DoCmd.OpenReport "Reports", acViewReport, , Me.Filter didn't seem to filter at all?

I keep thinking I'm learning but there are so many complexities!

Thanks for any pointers.

[aytee111]

You will get that error with the sWhere, unless you move the Dim to the top of the module (under Option Explicit) and change Dim to Public. Not sure why the Me.Filter didn't work, it works for me! Or maybe it should be Me.ctrpurchases.Form.Filter

[ScottishFi]

It worked!!!!! Yes your Me.ctrpurchases.Form.Filter did it. Thank you soo much. And thanks Ranman, I can see that would have worked if I'd got the syntax right around it.

I love this forum....