G
Guest
When you use a filter in Access, and the filter will result in no matches (0
records) the default behavior of Access to go into record Add mode (if you
have add rights), or just display a blank form (if you do not have add
rights). For my application either of these options are acceptable.
So, I tried adding some code to the apply filter event. The code looks like
the following:
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Check if no records return after filter. If so give message and
cancel filter. Otherwise
' the system will go into add mode for those will add rights.
On Error GoTo ApplyFilterError
If DCount("TransmittalID", "qryCDRTransmittal", Me.Filter) = 0 Then
MsgBox "Filter resulted in no records found. Filter is canceled",
vbExclamation, "No records found"
Cancel = True
End If
Exit Sub
ApplyFilterError:
MsgBox Err.Description & " Filter is canceled", vbExclamation, "Error
in Filter"
Cancel = True
End Sub
The error handling catches problems like trying to filter using text on a
numeric field.
However, the problem is this coding those not work for combo fields (works
fine for other fields). I have combo fields that use a lookup query to show
values linked to another table. The resulting filter, in me.filter, might
look like the following:
((Lookup_ToAFC.AFC="0651"))
It seems internally this works just fine for Access filtering. However,
DCOUNT, used above in the code, does not know how to handle this filter. I
get an error 2001, "You canceled the previous operator".
Has any had to deal with this type of problem before? Thanks for your help.
Regards,
Leif
records) the default behavior of Access to go into record Add mode (if you
have add rights), or just display a blank form (if you do not have add
rights). For my application either of these options are acceptable.
So, I tried adding some code to the apply filter event. The code looks like
the following:
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Check if no records return after filter. If so give message and
cancel filter. Otherwise
' the system will go into add mode for those will add rights.
On Error GoTo ApplyFilterError
If DCount("TransmittalID", "qryCDRTransmittal", Me.Filter) = 0 Then
MsgBox "Filter resulted in no records found. Filter is canceled",
vbExclamation, "No records found"
Cancel = True
End If
Exit Sub
ApplyFilterError:
MsgBox Err.Description & " Filter is canceled", vbExclamation, "Error
in Filter"
Cancel = True
End Sub
The error handling catches problems like trying to filter using text on a
numeric field.
However, the problem is this coding those not work for combo fields (works
fine for other fields). I have combo fields that use a lookup query to show
values linked to another table. The resulting filter, in me.filter, might
look like the following:
((Lookup_ToAFC.AFC="0651"))
It seems internally this works just fine for Access filtering. However,
DCOUNT, used above in the code, does not know how to handle this filter. I
get an error 2001, "You canceled the previous operator".
Has any had to deal with this type of problem before? Thanks for your help.
Regards,
Leif