DoCmd OpenForm using where..no data found

[Janet Thomas]

I am opening a form from another form using a command button. On click, I have Do.Cmd OpenForm...etc. with a where clause (built from information on this first form). This is working fine if I enter information which results in records being found.
My question is how to check if the where clause results in no data? I thought the following would work as I've used it in other forms (not ones where I'm passing a where clause though):

If Me.Recordset.RecordCount = 0 Then
MsgBox "No Data found for Selection - try again"
DoCmd.Close
End If

I have tried putting this on every event I can think of in the second form... Current, Open, Load, Got Focus, On Filter, Apply Filter - nothing works. I see a very brief flash of information and then my form goes completely grey. Can anyone help?

 

[Rich]

Form load or open
If RecordsetClone.RecordCount=0 Then
MsgBox "No Data found for Selection - try again"
DoCmd.Close
End If

 

[Janet Thomas]

I tried that and it doesn't work if there are no records. I have stepped through this in debug using a scenario where my filter should find 42 records out of 550. First it goes to the code for Form Current. Recordcount (of recordset and recordsetclose) is 550. Then it goes to Open - no change in recordcounts. Then it goes to Apply Filter - no change in recordcounts. Then it goes to the code for Current again. At this point, the recordcount (of recordset and recordsetclone) both change to 42. So that's fine.

When I use a scenario where by filter finds 0 records, everything above happens except it never goes to Form Current the second time! After Apply Filter, it just displays the grey form box.

Any other ideas?

(it now occurs to me that this thread maybe should have been on VBA rather than Forms - sorry for that.)

 

[Rich]

Sorry missed the filter bit, use the OnTimer event.
I suspect that the regular and consistent Members here read most if not all of the posts anyway, which topic to post under probaly isn't that critical

 

[Janet Thomas]

on Timer! That works. Not something I would have thought of. I am resetting the timerinteval to zero if there is data so it isn't repeated again.
Many, many thanks... I love this forum!