ACC1x: NWIND Orders Form Updates Order Amount Field with Zero

• Microsoft Access versions 1.0, 1.1

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

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.

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

Additional query words: bringing totals from sub to main

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