Form goes blank on filtering

[Tony G]

Hello World,

Problem: I have a simple form based on a three table query
that works well except when a filter is applied (via right-
click, filter for and entering say, London for a county
field) that results in zero records being returned.

When this happens the form goes totally blank and although
it does not hang it only returns to normal when the filter
is removed.

Question: Is it possible to trap this event and remove the
filter automatically should zero records be returned in
order to avoid freaking-out the user. I tried obvious
things like OnApplyFilter but these events seem to occur
before the query updates.

Any help would be appreciated.

 

[Van T. Dinh]

Check Access VB Help on the ApplyFilter Event of the Form. I think you can
use DCount or Recordset to check whether the criteria will return any record
or not and if no records, you can cancel the ApplyFilter Event.

 

[Tony G]

Thanks Van, you are the man.
Here's what I did for anyone with the same problem:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType
As Integer)
Dim z As Integer
z = DCount("[ContactID]", "CampaignsQuery", Me.Filter)
If z < 1 Then
MsgBox "cancel message here" vbInformation
Cancel = True
End If
End Sub