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

Thanks everyone for your suggestions. I really appreciate it.

Pretty much every software I know filters the way I have in mind.
You start typing text and the form starts filtering, you type too much text and eventually the form is empty because there are literally no results for whatever you typed. I mean, I guess I agree to disagree, but there's no way that leaving results in the subform list while the search box says "afddafasdfds" makes sense.

Anyway, yes a label would make sense, but without clearing the form it really doesn't really help. What if I have multiple search boxes? It becomes confusing.

I'm assuming this is just a limitation in access and I have to go through these workarounds to achieve what I need for this particular case.

arnelgp, thank you! I will attempt what you mentioned here and report back

arnelgp said:

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:Copy to clipboard

' 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

Click to expand...

Thanks arnelgp, I ended up with this solution. I wish I could hide the (New) line at the end for aesthetics but I think this is acceptable.

arnelgp said:

or add a msgbox telling there is no search result.

Click to expand...

In post #11 the OP stated he didn't want a msgbox.

arnelgp said:

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.

Click to expand...

Sorry you feel that way. I suspect what you're referring to involves other threads as well and not just this one. Those "contraventions" were questions regarding your posts to see if I missed the point of the problem being posted because it seemed one of us misinterpreted the issue. I could have imagined that it was something else. Or maybe you imagined that my intent was something else.

If the OP feels the same way, I think it is their post and their place to do so if they think I am out of line, not yours. Next time you have such a complaint, PM somebody rather than make your private complaint a public one. I only responded here because you did, otherwise I (hopefully) wouldn't.

Again, my apologies.