Cannot believe how many hours I spent on this.
I have a form that displays some data. I have a textbox that filters the form data after each key press.
It seems like VBA just cannot gracefully move on with its life if the Me.Filter returns 0 results.
This error happens because this form is set to "Allow Additions" false.
I cannot allow users to add new records in this form but I sure would like for them to be able to search properly.
I found a workaround that exits the sub if the recordset is empty but the textbox loses the focus so the user needs to click again on the search box.
I need to modify this so that if there are 0 results, for the love of god, leave the focus in the search box and let the user type as much as they want while displaying 0 results.
Here's a sample database https://gofile.io/?c=PvJhLg
I have a form that displays some data. I have a textbox that filters the form data after each key press.
It seems like VBA just cannot gracefully move on with its life if the Me.Filter returns 0 results.
This error happens because this form is set to "Allow Additions" false.
I cannot allow users to add new records in this form but I sure would like for them to be able to search properly.
I found a workaround that exits the sub if the recordset is empty but the textbox loses the focus so the user needs to click again on the search box.
I need to modify this so that if there are 0 results, for the love of god, leave the focus in the search box and let the user type as much as they want while displaying 0 results.
Here's a sample database https://gofile.io/?c=PvJhLg
Code:
Private Sub txt_searchall_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler
Dim filterText As String
'Apply or update filter based on user input.
If Len(txt_searchall.Text) > 0 Then
filterText = txt_searchall.Text
Me.Form.Filter = "[ClientName] LIKE '*" & filterText & "*' OR [ProjectNumber] LIKE '*" & filterText & "*'"
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
Exit Sub
End If
'Retain filter text in search box after refresh.
txt_searchall.Text = filterText
txt_searchall.SelStart = Len(txt_searchall.Text)
Else
' Remove filter.
Me.FilterOn = False
Me.Filter = ""
txt_searchall.SetFocus
End If
Exit Sub
errHandler:
'this is required, otherwise I get the Run Time Error 2185: You Can't Reference A Property for a Control Unless the Control Has the Focus
Me.Filter = ""
Me.FilterOn = False
End Sub
Last edited: