ACC1x: NWIND Orders Form Updates Order Amount Field with Zero

ID: Q101090

The information in this article applies to:

SYMPTOMS

The Order Amount field in the Orders table is incorrectly set to zero.


CAUSE

The Orders.Write Order Amount macro writes the order subtotal, calculated in the subform footer, to the Order Amount field in the Orders table before the order subtotal has finished calculating.

The order subtotal is calculated using the Sum() aggregate function, which runs asynchronously (or, independently) of other actions on the form. When a field is altered in the subform that affects this calculation (for example, the Quantity field), the AfterUpdate event for the subform executes the Orders.Write Order Amount macro. This macro executes before the new order subtotal can be calculated. As a result, the macro incorrectly updates the Subtotal field in the table.


RESOLUTION

The Order Subtotal field in the subform footer cannot be relied on to calculate the correct order subtotal, since it is being calculated independently of other events occurring on the form. Instead, the value should be calculated using the DSum() aggregate function in the Orders.Write Order Amount macro. This result can be written to the Order Amount field in the Orders table.


MORE INFORMATION

This change has already been made to the sample database NWIND.MDB that ships with Microsoft Access version 1.1. Following the "Steps to Reproduce Behavior" section is a description of changes that must be made to version 1.0 of the sample database NWIND.MDB to correct this problem. There are additional suggestions to optimize the Orders form for both versions 1.0 and 1.1.

Steps to Reproduce Behavior


  1. Open the sample database NWIND.MDB supplied with Microsoft Access version 1.0.


  2. Open the Orders form in Form view.


  3. Change the Quantity field for one of the products listed in the Orders Subform. (Note the Order ID of the order that you are changing.)


  4. Open the Orders table and find the record with the same Order ID.

    Result: The Order Amount field is set to zero.


Using DSum() Function to Correctly Calculate Order Subtotal

The following steps describe how to change the Orders form in Microsoft Access version 1.0 to implement the DSum() function:

NOTE: The sample database ORDENTRY.MDB supplied with Microsoft Access uses the same technique to calculate the order subtotal. Please see the FrmOrders.Recalc macro in ORDENTRY.MDB for more information.
  1. Open the Orders macro in Design view.


  2. Find the SetValue action for the Write Order Amount macro group.


  3. Change the expression argument from

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

    to

    DSum("[Extended Price]","[Order Details2]","[Order ID] = Forms![Orders]![Order ID]")


  4. Save the macro.



Because the Orders Subtotal field in the Order Subform footer is no longer necessary, use the following steps remove it and increase your database performance:
  1. Open the Orders Subform in Design view.


  2. Remove the Orders Subtotal field from the footer.


  3. Open the Orders form and select the Subtotal field. Change the the ControlSource property from

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

    to

    Order Amount


In Microsoft Access version 1.0, you can remove Orders.Write Order Amount from the BeforeUpdate property of the Orders form.

Additional query words: bringing totals from sub to main 


Keywords          : kbusage FmsSubf 
Version           : 1.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 25, 1999