Detect invisible form due to filter with no records

[krausr79]

I have a form based on a read-only query. It is a split form, so people can filter it. If a filter is applied that shows no records, the form disappears.

Well, the header doesn't disappear. I intend to have a message in the header show up when there are no records showing how to click 'Toggle Filter' so they can see the form again. My problem is that I can't find a good event to run this with.

So far, I've tried Form_Current, Form_ApplyFilter, and Form_AfterFinalRender. Finalrender never fired, applyfilter occurs before the filter is applied, and current doesn't fire when there are no records.

The code I am using to do this works if it can just happen when the form is blank:
Dim RS As Recordset
Set RS = Me.RecordsetClone
If RS.RecordCount = 0 Then
Me.lblNotice.Visible = True
Else
Me.lblNotice.Visible = False
End If
RS.Close
Set RS = Nothing

 

[Micron]

applyfilter occurs before the filter is applied

Double check this? AFAIK, if the filter is not applied, the event doesn't run when the ribbon button is clicked until a value is chosen and applied - then it runs. If it has already been applied, it runs when the ribbon button is clicked, which is before a new value can be chosen. Assuming I'm correct about that, I suggest that if a value is chosen you can do a DCount on the table or query behind the form using the filter as criteria. If the count is 0, cancel the filter. I confess I've never used this event, so I'm assuming 'Cancel As Integer' will allow cancelling the application of the filter, at which point you can message the user. You get the filter by referencing Me.Filter.

If they're picking a value from the filter list, isn't the only one that might cause this issue the "Blanks" option?