Updating calculated field in table without tabbing out of record.

  • Thread starter Thread starter Applebaum
  • Start date Start date
A

Applebaum

Hello,

I have a Purchase Order form which contains a subform of line items. The
main form is based on tblPurchaseOrders, the subform is based on tblItems.
The subform has a CostTotal field, as well as a text box CostOfAllItems
totaling all items =Sum([CostTotal]).

tblPurchaseOrders has a field POItemsTotal.

In the subform, in the AfterUpdate event of both Cost and Quantity is the
following code:
CostTotal = Quantity * Cost
Forms![frmPurchaseOrder]![POItemsTotal] = DSum("[CostTotal]", "PO_Items",
"[PurchaseOrderID]=[PO_ID]")

Yet I have the following problem:

In an existing purchase order, when I modify the quantity or the cost of a
line item, if I do not tab out of that record, neither the subform's
CostOfAllItems nor tblPurchaseOrders field POItemsTotal get updated. I
modify the qty or cost, close the form, check tblPurchaseOrders (it's not
updated), reopen the form, then check tblPurchaseOrders and at that point it
is updated.

If I tab to the next record, then both the subform's CostOfAllItems and
tblPurchaseOrders.POItemsTotal do get updated.

How can I make sure that the table gets updated when the form is closed,
regardless of whether the user tabs through to the end of the record or not?

Many thanks in advance!

Matthew
 
If it were me I wouldn't be storing all these totals at all, I'd just
calculate them each time they were needed.

HTH
Sam
 
Back
Top