Filter not clearing on subform (1 Viewer)

[Zippersabrat]

Hello all! I have a filter on a subform based on a value from the main form. Code for this filter is:

Private Sub Product_Type_AfterUpdate()

Me.Central_Structure_Product_List.Requery

Dim strType As String
Dim strFilter As String

If Not IsNull(Me.[Product Product Type]) Then
strType = "='" & Me.[Product Product Type] & "'"
Else
strType = "=" & Me.[Product Product Type] Like "" * """"
End If

strFilter = "[Product Type] " & strType & ""

With Me![Central Structure Product List].Form
.Filter = strFilter
.FilterOn = True
End With
End Sub

I am trying to get the filter to clear on the subform with a button click. So all records are shown. However, the below code is not doing this. Any suggestions?

Private Sub ClearBt_Click()

Me.Filter = ""
Me.FilterOn = True

End Sub

Thanks so much!

 

[theDBguy]

Hi. First question, is the subform linked to the main form using the Linked Master/Child Fields properties?

 

[Zippersabrat]

Hi. First question, is the subform linked to the main form using the Linked Master/Child Fields properties?

Yes. I could not get the first code to work without that.

 

[theDBguy]

Yes. I could not get the first code to work without that.

Okay then, if you're using a linked subform, then there is no way to display "all" records, unless by "all" you meant, basically, all the related records as defined by the linked fields. Is that what you meant?

 

[Zippersabrat]

I would like to be able to remove the filter to show ALL records. So any suggestions on how to get the first code set to work without using the linked master/child fields?

 

[theDBguy]

I would like to be able to remove the filter to show ALL records. So any suggestions on how to get the first code set to work without using the linked master/child fields?

Hi. Without seeing your file in action, I don't understand what you mean by the "first code" not working. If you don't link a subform to the main form, it should show all records. To filter it to something relating to what's on the main form, you can use its Filter property, which I think is what you're trying to use already. If you can post a demo version of your db, we can better understand your problem. Otherwise, if you want to show all the records in the subform, then you shouldn't use the Linked Master/Child Fields properties.

 

[Zippersabrat]

Hi, Sorry for the confusion. The first code is below. This code works if I manually change the [Product Product Type] field but doesn't work when you navigate through the records and it changes automatically. I do not want the user to change the [Product Product Type] field. It is actually invisible on the form so they cannot even see it. Maybe I need to put this code in another Sub?

Private Sub Product_Type_AfterUpdate()

Me.Central_Structure_Product_List.Requery

Dim strType As String
Dim strFilter As String

If Not IsNull(Me.[Product Product Type]) Then
strType = "='" & Me.[Product Product Type] & "'"
Else
strType = "=" & Me.[Product Product Type] Like "" * """"
End If

strFilter = "[Product Type] " & strType & ""

With Me![Central Structure Product List].Form
.Filter = strFilter
.FilterOn = True
End With
End Sub

 

[bastanu]

Yes, you need the code in the Current event of the form.

Cheers,
Vald

 

[theDBguy]

Hi, Sorry for the confusion. The first code is below. This code works if I manually change the [Product Product Type] field but doesn't work when you navigate through the records and it changes automatically. I do not want the user to change the [Product Product Type] field. It is actually invisible on the form so they cannot even see it. Maybe I need to put this code in another Sub?

Private Sub Product_Type_AfterUpdate()

Me.Central_Structure_Product_List.Requery

Dim strType As String
Dim strFilter As String

If Not IsNull(Me.[Product Product Type]) Then
strType = "='" & Me.[Product Product Type] & "'"
Else
strType = "=" & Me.[Product Product Type] Like "" * """"
End If

strFilter = "[Product Type] " & strType & ""

With Me![Central Structure Product List].Form
.Filter = strFilter
.FilterOn = True
End With
End Sub

Hi. Again, since I cannot see your form, I can't tell what's actually happening when you navigate through the records. But I'll just re-emphasize, using the Linked Master/Child Fields is sort of an "auto-filter" for the subform. So, when it's set, you can't see the entire recordset. If things are happening on your form when you navigate, either you have something in the Current event or the Linked Master/Child settings is messing with what you want to happen with your form. What happens if you simply empty out the Master/Child properties?

 

[Zippersabrat]

Yes, you need the code in the Current event of the form.

Cheers,
Vald

That did it! Thank you so much!

 

[Zippersabrat]

Hi. Again, since I cannot see your form, I can't tell what's actually happening when you navigate through the records. But I'll just re-emphasize, using the Linked Master/Child Fields is sort of an "auto-filter" for the subform. So, when it's set, you can't see the entire recordset. If things are happening on your form when you navigate, either you have something in the Current event or the Linked Master/Child settings is messing with what you want to happen with your form. What happens if you simply empty out the Master/Child properties?

I got it figured out! Thank you for your help!

 

[theDBguy]

I got it figured out! Thank you for your help!

Congratulations! Glad to hear you got it sorted out. Good luck with your project.