Here are some facts about #ERROR returned when a subform has no records:In these
examples, [Subf field] refers to a syntactically correctreference to a subform field from
a main form.
1. It will not evaluate to null: IsNull([Subf field]) is false
2. It will evaluate to an error on the main form, but not when passeto a global module:
on the main form evaluates to true
returns false, where this function exists in a global module:
Function IsAnError(testvalue as variant) as Boolean
3. It will not evaluate to numeric. IsNumeric([subf field]) evaluates to false.
I have found this to be the best test, since often it is a total or other number being
passed back to the main form. If not, there is usually some numeric field on the sub that
can be tested whether or not it is the field used on the main form.
This test indicates when there are records, IsNumeric will be true; when there are no
records, IsNumeric will be false. So, you can catch "no records" and display
what you want instead of #ERROR.
This function returns zero instead of #ERROR when used like this:nnz([Subf field]) on
the main form. I use it from a global module.
Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = 0
nnz = testvalue