ACC95: Domain Aggregate Functions Return #Error When Interrupted

Last reviewed: May 21, 1997
Article ID: Q148302
The information in this article applies to:
  • Microsoft Access version 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use Microsoft Access domain aggregate (totals) functions on a form, the functions may return the "#Error" message. Domain aggregate functions, such as DSum() and DCount(), evaluate an entire column or set of data (a domain).

CAUSE

This error occurs when focus is moved from the form's active control to any other control before the domain aggregate function has finished calculating. This behavior occurs with all domain aggregate functions.

RESOLUTION

Use one of the two following methods to work around this problem.

Method 1

  1. Open the form containing the domain aggregate function in Design view and set the form's OnCurrent property to the following event procedure

          Sub Form_Current()
          Me!<text box control name> = <expression>
          End Sub
    
       where <text box control name> refers to the name of the text box control
       on the form where you want to display the result of the expression and
       <expression> refers to the domain aggregate function expression used to
       complete your calculation.
    
    

  2. Select the text box control containing the domain aggregate function.

  3. Delete the formula currently in the ControlSource property.

Method 2

  1. Create the following new macro

          Macro Name       Macro Action
          -----------------------------
          DFunctionMacro   SetValue
    
          DFunctionMacro Actions
          -------------------------------
          SetValue
             Item: <text box control name>
             Expression: <expression>
    
          where <text box control name> refers to the name of the text box
          control on the form where you want to display the result of the
          expression and <expression> refers to the domain aggregate function
          expression used to complete your calculation.
    
    

  2. Select the text box control containing the domain aggregate function.

  3. Delete the formula currently in the ControlSource property.

  4. Set the form's OnCurrent property to the DFunctionMacro macro.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Problem

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.

  1. Open the sample database Northwind.mdb.

  2. Open Orders form in Design view.

  3. Add the following unbound text box to the form:

    NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the ControlSource property.

         Text box: MyDomain
         -------------------------------------------------------------
         ControlSource: (Choose one of the following Domain Aggregate
         functions)
    

         =DSUM("UnitPrice","Order Details Extended")*DSUM("ProductID","Order _
    
             Details  Extended")
    
         =DCOUNT("UnitPrice","Order Details Extended")*DCOUNT("ProductID", _
             "Order Details Extended")
    
    

  4. Switch the Orders form to Form view to ensure that the formula returns a value.

  5. Switch the Orders form back to Design view.

  6. Switch the Orders form back to Form view, and using the mouse, quickly place the pointer in the Salesperson combo box. This must be done while the domain aggregate function is calculating.

    NOTE: In most cases #Error is returned in the text box when the calculation is interrupted and fails. Whether or not the #Error occurs depends on how quickly the focus is moved from the active control.

REFERENCES

For more information about domain aggregate functions, search the Help Index for "domain aggregate functions."


Additional query words: dlookup davg dmax dmin dvar dstdev
Keywords : kberrmsg kbusage SynFnc
Version : 7.0
Platform : WINDOWS
Hardware : X86
Issue type : kbbug
Resolution Type : kbfix


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 21, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.