Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Forms: #Error when the Subform has no records

Author(s)
Keri Hardwick

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  ****************

© 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer