Access2000: How to check, is there records for estimated filtter condition

[Arvi Laanemets]

Hi

As I have catched cold lately, I'm in no form to find a solution myself.
I use a couple of controls on form to determine the filter condition. The
filter is set by procedure.

Private Sub cmdFilter_Click()
strFilter = <StringExpression>
Me.Filter = strFilter
Me.FilterOn = True
End Sub


It all works OK, but when there is set a filter condition, for which no
records are returned, then I encounter problems with calculated fields on
form, which are resulting in errors like "Invalid use of Null" etc.

What is best way to check for no rows returned, before the filter is set
actually - so I can break the procedure for such cases.


Thank in advance!
Arvi Laanemets

 

[Allen Browne]

It could be possible to DLookup() the RecordSource of the form, using
strFilter as the WhereCondition to see if there is a result, or you could
check the form's RecordsetClone.RecordCount (or just the form's NewRecord
property) after applying the filter.

The real solution, though will be to set up the form's calcualted fields so
they cope with the situation. For example, if you have this kind of field:
DLookup("CustomerID", "Table1", "OrderID = " & [OrderID])
then when OrderID is null (e.g. at a new record), the 3rd argument becomes
just:
OrderID =
which is mal-formed. To avoid that situation, use Nz() to supply some value,
e.g.:
DLookup("CustomerID", "Table1", "OrderID = " & Nz([OrderID],0))

If your form does not accept new records (e.g. its AllowAdditions property
is No, or its RecordSource is a read-only query), the form's Detail section
goes completely blank when it is filtered so no records are returned. In
this case, referring to the non-existent controls generates an error, and
even referring to unbound controls in the Form Header and Form Footer can
also cause errors. The simplest workaround for this situation is to set the
form's AllowAdditions property to Yes, so it can show the new record, and
prevent the addition by cancelling Form_BeforeInsert instead. A read-only
RecordSource is not quite so simple: if you have that situation, you can use
IIf() to test if there are records, e.g.:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, Null, Sum([Amount]))

Hope you are over that cold soon, and feeling up to tackling db problems
again.