Handling no results for docmd.applyfilter

Question

Hi,

I have an Access app where I use search functionality. I have a TextBox and a Search Button on the form, and it does a wilcard search of whatever the user enters in the TextBox, and displays the records in other controls (Text and Combo Boxes).

To achieve this, I am using the DoCmd.ApplyFilter method and I wanted to know if there is a way that I can handle the 'no results' scenario? What it does currently is shows all the display controls as empty, and the ID field says (AutoNumber) as if I was adding a new record.

If it is not possible to handle no results with DoCmd.ApplyFilter, then is there another way to search the records and handle the 'no results' scenario as well?

Thank you!

Answer 1

You can check the recordsetclone to see if there are any records and remove the filter if there are not.

DoCmd.ApplyFilter , "id=5"
If Me.RecordsetClone.RecordCount = 0 Then
    MsgBox "No records"
End If