ACC: How to Store Subform Totals in a Main Form Field (1.x/2.0)

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

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

This article describes how to compute a sum of values in a subform, and store that value in a field on a main form.

This article assumes that you are familiar with constructing and using a main form and subform in a one-to-many relationship.

MORE INFORMATION

Drawbacks to Storing Calculated Values

Although this article demonstrates how to store a subform total in a field on a main form, it should be noted that it is not good database design to store computed values for the following reasons:

• Storing calculated values occupies additional space in your database.
• You run the risk of violating the integrity of your data. If you open the subform and change one of the values that is summed, this change will not be reflected in the total stored in the main form, making the data inconsistent.

  Note that the sample database NWIND.MDB shipped with Microsoft Access version 2.0 does not store the order amount from the Order Details table in the Orders table as earlier versions did. Instead, it computes the totals in a query that is based on the Order Details table. This query is grouped on the Order ID field in the first column, and computes the total in the second column. This query can be joined to the Orders table in another query to see order information just as though the total had been stored in the Orders table. This method is not only the preferred database design, it is also easier to implement than storing the total as described below.

How to Store Computed Subform Totals in a Main Form

This section describes how the sample database NWIND.MDB shipped with Microsoft Access version 1.1 computes the order amount in the Order Details subform and stores that value in the Order Amount field in the main form based on the Orders table.

In order to correctly compute and store the order amount, a macro must be run to recompute and store the amount whenever information for the order changes. This is accomplished in the Orders form by running the macro specified in the AfterUpdate property setting of the Unit Price, Quantity, and Discount fields. A macro is also specified in the AfterUpdate property setting of the Orders Subform form so that when the record is saved, the order amount is recomputed and stored.

The following macro, called Orders.Update Order Amount, is specified in the AfterUpdate property setting of the Unit Price, Quantity, and Discount fields:

   Action       Description
   -----------------------------------------------------------------
   DoMenuItem   Update the Order Subtotal control after changing
                values in any of the following controls: Unit Price,
                Quantity, or Discount.
   RunMacro     Run the Write Order Amount macro.

   Orders.Update Order Amount Actions
   ----------------------------------
   DoMenuItem
      Menu Bar: Form
      Menu Name: File
      Command: Save Record
   RunMacro
      Macro Name: Orders.Write Order Amount

The Orders.Write Order Amount macro is specified in the AfterUpdate property setting of the Order Details subform and, as seen above, is invoked from the Orders.Update Order Amount macro. The Orders.Write Order Amount macro has the following actions:

   Action     Description
   -----------------------------------------------------------
   SetValue   Write the value in the Order Amount control into
              the Order Amount field in the Orders table.

   Orders.Write Order Amount Actions
   -----------------------------------------------------------
   SetValue
      Item: Forms![Orders]![Order Amount]
      Expression: DSum("[Extended Price]","[Order Details2]",
                       "[Order ID]=Forms![Orders]![Order ID]")

The DSum() expression computes the sum of line item values by summing the Extended Price field in the Order Details2 query where the Order ID in the Order Details table matches the current Order ID in the Orders form.

Using the DSum() Function Instead of Summing in the Subform Footer

A common technique for computing the sum of values in subform fields is to include an expression in the subform footer that uses the Sum() function, and then reference this sum field from the main form. The Orders form demonstrates this technique. The subform footer has a text box called Order Subtotal with the following expression:

   =Sum([Extended Price])

   =[Orders Subform].Form![Order Subtotal]

   Forms![Orders]![Orders Subform].Form![Order Subtotal]

This problem is described in more detail in the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q101090
   TITLE     : ACC1x: NWIND Order Form Updates Order Amount Field with Zero

REFERENCES

For more information about the DSum() function, search for "DSum," and then "DSum Function" using the Microsoft Access Help menu.

For more information about domain aggregate functions such as the DSum() function, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q108098
   TITLE     : ACC1x: DLookup() Usage, Examples, and Troubleshooting Tips
               (1.x)

Microsoft Access "User's Guide," version 1.1, Chapter 11, "Using Expressions in Forms," pages 300-302