Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Avoid #Error in form/report with no records

        0 votes: *****     4,76 views      No comments
by Allen Browne, 18 January 2008    (for Access v2+)

Flaws in Microsoft Access

Provided by Allen Browne, January 2008.


Avoid #Error in form/report with no records

Calculated expressions show #Error when a form or report has no records. This sort-of makes sense: if the controls don't exist, you cannot sum them.

In forms

The problem does not arise in forms that display the new record. It does occur if the form's Allow Additions property is Yes, or if the form is bound to a non-updatable query.

To avoid the problem, test the RecordCount of the form's Recordset. In older versions of Access, that meant changing:
    =Sum([Amount])
to:
    =IIf([Form].[Recordset].[RecordCount] > 0, Sum([Amount]), 0)

Access 2007 has a bug, so that expression fails, even with SP1 applied. You need a function.

Copy this function into a standard module, and save the module with a name such as Module1:

Public Function FormHasData(frm As Form) As Boolean
    'Purpose:   Return True if the form has any records (other than new one).
    '           Return False for unbound forms, and forms with no records.
    'Note:      Avoids the bug in Access 2007 where text boxes cannot use:
    '               [Forms].[Form1].[Recordset].[RecordCount]
    On Error Resume Next    'To handle unbound forms.
    FormHasData = (frm.Recordset.RecordCount <> 0&)
End Function

Now use this expression in the Control Source of the text box:
    =IIf(FormHasData([Form]), Sum([Amount]), 0)

Notes

  1. Leave the [Form] part of the expression as it is (i.e. do not substitute the name of your form.)
  2. For Access 97 or earlier, use RecordsetClone instead of Recordset in the function.
  3. A form with no records still has display problems. The workaround may not display the zero, but it should suppress the #Error.

In reports

Use the HasData property property, specifically for this purpose.

So, instead of:
    =Sum([Amount])
use:
    =IIf([Report].[HasData], Sum([Amount]), 0)

If you have many calculated controls, you need to do this on each one. When Access discovers one calculated control that it cannot resolve, it gives up on calculating the others. Therefore one bad expression can cause other calculated controls to display #Error, even if those controls are bound to valid expressions.

For details of how to do this with subreports, see Bring the total from a subreport onto a main report.


Home Index of tips Top

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


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Avoid #Error in form/report with 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


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