When Filtering Form and no results are returned. Handle the search box gracefully (1 Viewer)

heathxp · Jul 5, 2019

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

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

Micron · Jul 5, 2019

Hope you don't mind an answer after only a cursory view.

I would not use a Mouse event - they can present issues. You ought to use the textbox Change event for a "find as you type" (FAYT) feature and can use the Text property reliably since the control obviously has the focus when this event runs.

Or you can consider that when a filter is applied, the form can get refreshed or requeried, which means that focus will shift to the control that is 1st in the tab order. You can ensure that your preferred control is first in the order, or programmatically set focus to it.

heathxp · Jul 5, 2019

Micron said:
Hope you don't mind an answer after only a cursory view.

I would not use a Mouse event - they can present issues. You ought to use the textbox Change event for a "find as you type" (FAYT) feature and can use the Text property reliably since the control obviously has the focus when this event runs.

Or you can consider that when a filter is applied, the form can get refreshed or requeried, which means that focus will shift to the control that is 1st in the tab order. You can ensure that your preferred control is first in the order, or programmatically set focus to it.

Micron. Not sure where you see a mouse event. I'm using the key_up event of a search text box.
I am indeed refocusing on the textbox everytime I filter but when 0 results are displayed, it looks the form completely removes the focus from any of the form elemets and I cannot set it back so that the user can continue to enter the text without being interrupted.

Here's a sample database:
https://gofile.io/?c=PvJhLg

Micron · Jul 6, 2019

I mis-keyed. Meant to say KeyPress, but same sentiment applies. Will take a look at your db and see if I have any questions.

EDIT
Maybe not. Can you not post zipped copy here? Some will not download from external sources. I don't like to but waffle on that some times, to be honest. I trust this site - not so much external ones.

heathxp · Jul 6, 2019

Micron said:
I mis-keyed. Meant to say KeyPress, but same sentiment applies. Will take a look at your db and see if I have any questions.

EDIT
Maybe not. Can you not post zipped copy here? Some will not download from external sources. I don't like to but waffle on that some times, to be honest. I trust this site - not so much external ones.

Sure. Uploaded the file directly here. Thank you

arnelgp · Jul 6, 2019

haven't download your db.
but there is also another way to prevent users from adding records to your form.
first, set the Allow Additions to Yes.
then, add code to form's Before Insert event:

Code:

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
End Sub

this will prevent any additions of records.

Micron · Jul 7, 2019

arnelgp said:
but there is also another way to prevent users from adding records to your form.
first, set the Allow Additions to Yes.
then, add code to form's Before Insert event:

Code:

Private Sub Form_BeforeInsert(Cancel As Integer) Cancel = True End Sub

this will prevent any additions of records.

I think the real issue is the error during filtering and inability to keep focus on a control?

To that end:
Option Compare Database
Option Explicit <<<turn this on!! vbe>options>require variable declaration

As noted, my preference is Change, not key event - your choice

Code:

Private Sub txt_searchall_Change()
Dim filterText As String
Dim lngstart As Long

Me.txt_searchall.SetFocus
filterText = Me.txt_searchall.Text
lngstart = Len(txt_searchall.Text)

If lngstart > 0 Then
   With Me
     .Form.Filter = "[ClientName] LIKE '*" & filterText & "*' OR [ProjectNumber] LIKE '*" & filterText & "*'"
     .FilterOn = True
     If .Recordset.RecordCount = 0 Then
        .FilterOn = False
        MsgBox "No records with that search term."
     End If
       .txt_searchall.SetFocus
       .txt_searchall.SelStart = lngstart + 1
   End With
Else
    Me.FilterOn = False
    Me.txt_searchall.SetFocus
 End If


 End Sub

I think you'll find that it works OK regardless if there are records plus if user backspaces and clears contents, filter goes off and focus stays on search box. Bonus - cursor stays at the end of the string after every filter change.

Hope that helps.

arnelgp · Jul 7, 2019

this is my solution on focus problem.

Code:

Private Sub txt_searchall_KeyUp(KeyCode As Integer, Shift As Integer)
Dim lngCount as Long
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
   ' don't filter it yet
   lngCount=Nz(DCount("1", "yourTableName", "[ClientName] LIKE '*" & filterText & "*' OR [ProjectNumber] LIKE '*" & filterText & "*'"), 0)
   If lngCount = 0 Then Exit Sub
   Me.Form.Filter = "[ClientName] LIKE '*" & filterText & "*' OR [ProjectNumber] LIKE '*" & filterText & "*'"
   Me.FilterOn = True
  '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 = ""
End If
 txt_searchall.SetFocus

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[/code]

heathxp · Jul 9, 2019

arnelgp said:
this is my solution on focus problem.

Thanks arnelgp. This fixes the focus problem but it will leave some results even if there's no match. Meaning if I type quickly some gibberish it will still display all the results instead of an empty list.

Mark_ · Jul 9, 2019

You can always use DCount() to validate that your filter WILL return results before changing your filter. This will avoid issues with no results returned.

When you have no results, what do you want to present to the user and how do you want it handled?

heathxp · Jul 9, 2019

Micron said:
I think the real issue is the error during filtering and inability to keep focus on a control?

To that end:
....

I think you'll find that it works OK regardless if there are records plus if user backspaces and clears contents, filter goes off and focus stays on search box. Bonus - cursor stays at the end of the string after every filter change.

Hope that helps.

Hi Micron, thank you for those tips. Yes, both of these solutions work but I was trying to make this a bit more user-friendly and not present a message box if there are no results. Instead, I wanted to clear the list and display no results. This is where all the trouble starts.

heathxp · Jul 9, 2019

Mark_ said:
You can always use DCount() to validate that your filter WILL return results before changing your filter. This will avoid issues with no results returned.

When you have no results, what do you want to present to the user and how do you want it handled?

Simply display an empty list with no results. This is possible if I change the form "Allow Additions" to true. So I might go with the solution arnelgp described to do it programmatically. Although this solution is not proactive, it will still display the * "add new record" line to the end user.

Micron · Jul 9, 2019

heathxp said:
I was trying to make this a bit more user-friendly and not present a message box if there are no results.

The message part was a freebie. Don't use it then - do anything else, including possibly nothing.

heathxp · Jul 9, 2019

Micron said:
The message part was a freebie. Don't use it then - do anything else, including possibly nothing.

Haha. That's the whole point why I created this thread. I just want simple and intuitive filtering.

"When Filtering Form and no results are returned. Handle the search box gracefully"

If I take your example and remove the message if there are no results the list will not be filtered but it will remain at the last known filter. Instead, I just want the list to be empty when there are no results.

Mark_ · Jul 9, 2019

Please remember, what is intuitive to one person is not intuitive to another.

For myself, I would have used a subform that gets filtered from the parent. If the filter returns no values you could hide the subform with a large label that says "No records match filter criteria", thus making sure the user knows there are no matching records.

This would also easily allow you to prevent users from entering records or thinking they could.

arnelgp · Jul 10, 2019

or add a msgbox telling there is no search result.

Micron · Jul 10, 2019

or add a msgbox telling there is no search result.

it was already stated that a message box was not wanted?

Edited - misinterpreted the last OP comments.

Micron · Jul 10, 2019

Now an update.
IMHO the problem is because it's a split form. Everything I played with
- allowing additions
- removing the filter from property [don't need property setting + code]
- explicitly setting focus before trying to get text AND
- setting focus to a textbox (no border, no back color to make it "invisible" but selectable) so as to get .Value instead of text (search control would be updated)
FAILED
until I made it a form/subform. Then setting focus and using properties was possible without error.

arnelgp · Jul 10, 2019

mr.micron you are always conteavening my answer. my answers are for the ops and not for you. it is not you who will pick the most favorable opinion. just answer the op and ignore my post. have some manners.

arnelgp · Jul 10, 2019

mr.op, imo the best thing is to allow addition to your form and in the before insert cancel it. on my test it does not let the search textbox to go out of focus even when no records matched. to remove the "New" while on new record, remove the auto number field feom the form.

Code:

' Allow Addition = Yes
Private Sub Form_BeforeInsert(Cancel As Integer)
    Cancel = True
End Sub

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.
    Me.txt_searchall.SetFocus
    filterText = txt_searchall.Text & ""
    If Len(filterText) > 0 Then

        Me.Form.Filter = "[ClientName] LIKE '*" & filterText & "*' OR [ProjectNumber] LIKE '*" & filterText & "*'"
        Me.FilterOn = True
        'Retain filter text in search box after refresh.
        With txt_searchall
            .SetFocus
            .Text = filterText
            .SelLength = 0
            .SelStart = Len(txt_searchall.Text)
        End With

    Else
        ' Remove filter.
        Me.FilterOn = False
        Me.Filter = ""
        txt_searchall.SetFocus
    End If
    Exit Sub
ErrHandler:
    Resume Next
End Sub

When Filtering Form and no results are returned. Handle the search box gracefully (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

..forever waiting... waiting for jellybean!

AWF VIP

..forever waiting... waiting for jellybean!

Registered User.

Longboard on the internet

Registered User.

Registered User.

AWF VIP

Registered User.

Longboard on the internet

..forever waiting... waiting for jellybean!

AWF VIP

AWF VIP

..forever waiting... waiting for jellybean!

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread