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

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 DoMenuItem action saves the Order Details record with the call to the Orders.Write Order Amount macro so that the new order can be computed.

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]")
				

It should be noted that the description for the SetValue action above is misleading. The action is not really writing the value in the Order Amount control, it is instead computing the total using a DSum() function and writing this value to the Order Amount field.

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])
				

The Extended Price field is computed in the Order Details2 query that the subform is based on. The Extended Price field is computed by multiplying the Unit Price and Quantity fields, and includes a discount. This expression is displayed on the main Orders form in a text box called Subtotal with the following expression:

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

It would seem that you could use this value in the Orders.Write Order Amount macro instead of the DSum() expression. However, there are problems associated with doing this. The Orders.Write Order Amount macro in the NWIND database shipped with Microsoft Access version 1.0 does use the subform Sum() expression. In place of the DSum() expression, the SetValue action writes the following:

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

The problem with this method is that the expression may not always immediately reflect the current order amount. The value is computed independently of other actions on the form. When a field that affects this calculation is altered in the subform, the Orders.Write Order Amount macro may be run by the form's AfterUpdate property before Microsoft Access has a chance to recompute the new order amount in the subform. This results in incorrect values being stored.

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

101090 ACC1x: NWIND Order Form Updates Order Amount Field with Zero

This problem no longer occurs in the NWIND database shipped with Microsoft Access version 1.1 because the DSum() expression is used instead of referencing the Order Subtotal field. This explains why the description for the SetValue action in the NWIND database shipped with Microsoft Access 1.1 incorrectly reads "Write the value in the Order Amount control..." as described earlier.