Repost: No results on filtered query in subform

[Christine Pearc]

I haven't received any response to my post of 27 October and hope that by reposting it someone can help, since I've yet to figure out how to programme what I need. Any takers?

Thanks, Christine.

The main part of my form has fields for selecting multiple search criteria. The subform shows the results of the search. (Based on Dave Eyley’s MultiCriteriaSearch demo from here.)

Me.Searchstr = Searchstr
Me.Respond_Search_subfrm.Form.Filter = Searchstr
Me.Respond_Search_subfrm.Form.FilterOn = True

It works great (thanks to Dave for sharing his demo). However, if the filter on the underlying query produces no results, I want to display a message to the user. The main form and the subform have the same query as the record source. I've looked all over this forum and the web for answers and found the following 2 snippets which looked like they would do the trick. I’ve tried them in the main form (immediately after the commands shown above) and in all sorts of places in the Main form and in the subform, but the code isn't being "hit" (no msg box).

Dim iCount As Integer
iCount = DCount("*", "qryRespondSearch")
If iCount = 0 Then
MsgBox "No records found matching the search criteria"
End If

---OR---

If RecordsetClone.RecordCount = 0 Then
MsgBox "No records found matching the search criteria"
End If

Could someone advise what I’m doing wrong, where to put either of the above bits?

 

[Jings00]

Maybe this would help- does your query itself have records returned? that in turn are not displayed due to the filter. If this is the case your code to check for no records would not consider the filter, and therefore never return a no records result. I would not consider myself code savy enough to suggest the best solution, but you may consider having your original code change the actual recordset of the subform, not the filter properties. I am not a code guru in any sense, so maybe some others here can suggest some way to check for no results upon filtering.