Filter for Subform not working as intended (1 Viewer)

Zippersabrat

Blah Blah Blah :o)
Local time
Today, 14:30
Joined
May 5, 2010
Messages
31
I am going nuts with this. No errors and it filters perfectly on the Product Group but it will not filter on Product Type. Help! :banghead:

The goal is to filter a subform [Central Structure Product List] based on given values for Product Group and Product Type. I have another code on both fields to re-filter after update and those work great. Just cannot get this one to do both fields at once on load.

And yes, I shamelessly stole this code from another post. ;)

Code:
Private Sub Form_Load()

Dim StrFilter1 As String
Dim StrFilter2 As String

Dim StrFilter As String

If IsNull(Me.ProductGroup.Value) Then
StrFilter1 = "Like '*'"
Else
StrFilter1 = "='" & Me.ProductGroup.Value & "'"
End If

If IsNull(Me.ProductType.Value) Then
StrFilter2 = "Like '*'"
Else
StrFilter2 = "='" & Me.ProductType.Value & "'"
End If

StrFilter = "[CSProductType] " & StrFilter2 & " And [CSProductGroup] " & StrFilter1 & ""

With Me![Central Structure Product List].Form
.Filter = StrFilter
.FilterOn = True
End With
    
[Label761].Visible = False
[Details].Visible = False
[Question].Visible = False
[Label760].Visible = False
[Label869].Visible = False
[CentralCode].Visible = False

End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:30
Joined
May 21, 2018
Messages
8,463
The biggest thing is debug this with a print so you can see what the issue is. The common problem is forgetting spaces.

"productType = 'type1'AND ProductGroup='SomeGroup'"
so add

StrFilter = "[CSProductType] " & StrFilter2 & " And [CSProductGroup] " & StrFilter1 & ""
debug.print strFilter and try all combinations of the controls
I cannot say I see an immediate problem but the debug will usually make it obvious.

I do it a little different.
Code:
Dim StrProductGroup As String
Dim StrProductType As String

Dim StrFilter As String

If not IsNull(Me.ProductGroup.Value) Then
  StrProductGroup = "[CSProductGroup] ='" & Me.ProductGroup.Value & "'"
End If

If not IsNull(Me.ProductType.Value) Then
  StrProductType = "[CSProductType] ='" & Me.ProductType.Value & "'"
End If

If strProductType <> "" and strProductGroup <> "" then
  strFilter = StrProductType & " AND " & strProductGroup
else
  strFilter = strProductType & strProductGroup
end if 
debug.print strFilter

if Not trim(strFilter) = "" then
 With Me![Central Structure Product List].Form
  .Filter = StrFilter
  .FilterOn = True
  End With
else
  With Me![Central Structure Product List].Form
  .Filter = ""
  .FilterOn = False
  End With
end if
 

Zippersabrat

Blah Blah Blah :o)
Local time
Today, 14:30
Joined
May 5, 2010
Messages
31
Thank you! It didn't work but it did lead me down the right path. Turned out a different set of codes on the On_Current was doing all the work. Once I blocked that and moved this code to On_Current it worked. I did use your tricks to clean it up though.

Thanks again!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:30
Joined
May 21, 2018
Messages
8,463
Turned out a different set of codes on the On_Current was doing all the work.
This is why the debug.print or message box can be your best friend for debugging. Will tell you if the code is actually firing and allow you to see what is going on.
 

Users who are viewing this thread

Top Bottom