Solved Sub procedure to filter different textboxes in subform

[silentwolf]

Hi guys,

I like to create one filter procedure for different textboxes in a subform

With this code below I am able to filter different textboxes in a subform.

Private Sub txtContactNumber_AfterUpdate()
    If Not IsNull(Me.txtContactNumber) Then
        Me.Filter = "[KndNr] LIKE '*" & Me.txtContactNumber & "*'"
        Me![sfmAllContacts].Form.Filter = Me.Filter
        Me![sfmAllContacts].Form.FilterOn = True
    End If
End Sub

Private Sub txtPLZ_AfterUpdate()
    If Not IsNull(Me.txtPLZ) Then
        Me.Filter = "[PLZ] LIKE '*" & Me.txtPLZ & "*'"
        Me![sfmAllContacts].Form.Filter = Me.Filter
        Me![sfmAllContacts].Form.FilterOn = True
    End If
End Sub

However I like to create one sub procedure to filter the same subform so the code gets a little shorter
This is what I tried

Private Sub UpdateData(ByRef ipTextbox As TextBox, ByRef ipForm As SubForm, ByRef ipField As String)
    If Not IsNull(ipTextbox) Then
'        Me.Filter = "[ipField] LIKE '*" & ipTextbox & "*'"
'        Me.Filter = ipField Like " * " & ipTextbox & " * """
'        Me.Filter = ipField Like " * " & ipTextbox & " * "
        ipForm.Form.Filter = Me.Filter
        ipForm.Form.FilterOn = True
    End If
End Sub

Private Sub txtContact_AfterUpdate()   
    Call UpdateData(Me.txtContact, Me.sfmAllContacts, "KundeName")       
End Sub

The issue is in the filter ipField.
Could someone tell me how I need to change that line in red so it would work please`?


Cheers
Albert

 

[cheekybuddha]

Me.Filter = "[" & ipField & "] Like '*" & Replace(ipTextbox & vbNullString, "'", "''") & "*'"

 

[silentwolf]

Hi David,

many thanks! It works fantastic!!

Cheers

 

[ebs17]

' after each attempt:
Debug.Print Me.Filter   ' or a corresponding string variable

As a qualified developer, you know what a working filter should look like. So you look at the result of the composition via VBA and compare it with the given goal. In the event of an error, you can see what needs to be changed.

 

[cheekybuddha]

You're welcome, Albert

To make it slightly more generic and reusable you might consider declaring the ipForm parameter As Form, rather than As Subform.

That way you can use the code on either a normal form or a subform, though you *may* have to adjust the call when referring to a subform to:

Call UpdateData(Me.txtContact, Me.sfmAllContacts.Form, "KundeName")

 

[silentwolf]

Hi guys,

@ebs17
cheers for pointing that out to me! I was not thinking about that!

@cheekybuddha
cheers I will change that! Many thanks!!

Cheers
Albert