'#Error' when a Subform has no records

        3 votes: *****     4,660 views      No comments
by Keri Hardwick, 01 January 1999    (for ALL VERSIONS of Access)

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:
            IsError([Subf field]) on the main form evaluates to true
            IsAnError([Subf field]) returns false, where this function exists in a global module:

    Function IsAnError(testvalue as variant) as Boolean
            IsAnError = IsError(testvalue)
    End Function
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.

'***************** Code Start ***************
'This code was originally written by Keri Hardwick. 
'It is not to be altered or distributed, 
'except as part of an application. 
'You are free to use it in any application,  
'provided the copyright notice is left unchanged.
'Code Courtesy of
'Keri Hardwick
Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
    If Not (IsNumeric(testvalue)) Then
        nnz = 0
        nnz = testvalue
    End If
End Function
'***************** Code End  ****************

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

Have your say - comment on this article.

What did you think of ''#Error' when a Subform has no records'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).