'#Error' when a Subform has no records

        3 votes: *****      4,504 views      0 comments
by Keri Hardwick, 01 January 1999    (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
    Else
        nnz = testvalue
    End If
End Function
'***************** Code End  ****************

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your 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...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.