dynamic search box filters using LIKE-CodePudding

I have a dynamic search box filtering a subform based on user input. I also have a few filter buttons that filter the same subform. I set up the search box to incorporate preexisting filters applied by those buttons.

All that works fine. The problem I have is:

The dynamic filter using the LIKE statement only seems to work correctly when the length of the filter text is >= 3. Go below that and it applies some wonky filtering. Maybe I am using the statement wrong. I thought it would look if the field's value contains the search string somewhere in it. But it seemingly accepts values like "Natronlauge 50% techn. EN 896" for a search string of "Hä", which seems weird to me. It works once I add a third character though. Lines 11 and 13:

Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe & "*' AND [kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'"

Would be nice if someone has some ideas how to go about these issues. Here the full code for my searchbox:

Private Sub SearchBoxStoffe_KeyUp(KeyCode As Integer, Shift As Integer)

On Error GoTo errHandler

Dim filterText As String

'Apply dynamic filter for current filter category.
If Len(SearchBoxStoffe.Text) > 0 Then
    filterText = SearchBoxStoffe.Text
    If Forms![HUB]![FilterAlleLink] = "" Then
        Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe & "*'"
    Else
        Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe & "*' AND [kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'"
    End If
    Me.FilterOn = True
    'Retain filter text in search box after refreshing.
    SearchBoxStoffe.Text = filterText
    SearchBoxStoffe.SelStart = Len(SearchBoxStoffe.Text)
Else
    'Revert to current main filter category.
    If Forms![HUB]![FilterAlleLink] <> "" Then
        Call FilterStoffe("[kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'")
    Else
        If Forms![HUB]![FilterAlleLink] = "" Then
            Me.Filter = ""
            Me.FilterOn = False
        End If
    End If
End If

'Set focus back to search box
SearchBoxStoffe.SetFocus

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

CodePudding user response：

The dynamic filter using the LIKE statement only seems to work correctly when the length of the filter text is >= 3. Go below that and it applies some wonky filtering.

I don't really know why this is happening, but try the below. It makes use of the Change() event and covers 4 scenarios:

Both filters applicable.
Search Box only.
Main category only.
Nothing (clear the filter).

Also, I don't know what the FilterStoffe() method does, but I assume it just applies the main filter only.

Private Sub SearchBoxStoffe_Change()
    On Error GoTo Trap
                
    Select Case True
            
        'both filters
        Case Len(SearchBoxStoffe.Text) > 0 And Not IsNull(Forms.HUB.FilterAlleLink.Value):
            Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe.Text & "*' AND [kategorie] = '" & Forms.HUB.FilterAlleLink.Value & "'"
            FilterOn = True
            
        'SearchBox only
        Case Len(SearchBoxStoffe.Text) > 0:
            Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe.Text & "*'"
            FilterOn = True
         
        'FilterAlleLink only
        Case Not IsNull(Forms.HUB.FilterAlleLink.Value):
            Form.Filter = "[kategorie] = '" & Forms.HUB.FilterAlleLink.Value & "'"
            FilterOn = True
            
        'Nothing
        Case Else:
            FilterOn = False
            Filter = vbNullString
    End Select
        
Leave:
    On Error Resume Next
    SearchBoxStoffe.SetFocus
    Exit Sub
    
Trap:
   MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
   Resume Leave
End Sub

Keep in mind, within the Change() event, the Text property gets updated with every keystroke and when the control loses the focus, it gets copied to the Value property.

However, the Value is the default property when you just reference the control.

So this

Me.SearchBoxStoffe

is the same as this:

Me.SearchBoxStoffe.Value

CodePudding user response：

There were 2 issues that prevented the searchbox from running as intended:

Object references in the project were created with 2 different language versions of access. Objects would call other objects using the formulation of one language, which in turn called objects using referencing in another language etc. In cases where fields and/or queries would return empty, this would cause some of the references to no longer function as intended. The result was the program running out of stack space, empty controls on subforms that returned empty queries, objects not being found and more.
The searchbox filter was lagging behind the text in the searchbox by one event. If entering a new search string, the filter applied would always be missing the last character when using SearchBoxStoffe in the filter statement. Entering "Wood" would cause the filter to apply "Woo" etc.

The solutions are the following:

Fix all the references in the file manually to either language version and do not mix them up going forward.
The Value of the search box SearchBoxStoffe is not yet updated on either the KeyUp or the Change event when entering a new character. This can be fixed by substituting the Text value instead, which is updated already. Simply change line 11 to Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe.Text & "*'" and line 13 to Me.Form.Filter = "[bezeichnung] LIKE '*" & SearchBoxStoffe.Text & "*' AND [kategorie] = '" & Forms![HUB]![FilterAlleLink] & "'". The info originally came from @KostasK in his solution:

Keep in mind, within the Change() event, the Text property gets updated with every keystroke and when the control loses the focus, it gets copied to the Value property.

Which works too btw, just wasn't able to be verified since issue 1 prevented the code from running correctly. Answer by Kostas K.