Why is After Update DSUM Before Update Value

Question

I have a subForm, aContinuous Form, filtered by the field JobItemId, that has a field Price, after price is updated I wish to determine the new total of Prices for JobItemId and pass this for processing elsewhere.

I have this code:

Private Sub Price_AfterUpdate()
   Dim TotalCost As Currency
   Dim strFilter As String
   strFilter = "[JobItemID] = " + Str(JobItemID.Value)
   TotalCost = DSum("[Price]", Me.Recordset.Name, strFilter)
   Me.Parent.Total = TotalCost
   Me.Parent.Update_FinalPrice
End Sub

I set a watch on TotalCost so that I could verify the value that was being passed, and found that the total was the value before the update. Why is this so, and what do I need to do to get the correct after update total?

Answer 1

Your code is firing in the AfterUpdate event for the control. You need to move it to the AfterUpdate event for the form (ie, Form_AfterUpdate).

DSum operates on the records committed to the database, but even though your price has been updated in the AfterUpdate event for the Price control, it has not yet been committed to the database (Access indicates this by showing the writing pencil in the record selector). Immediately after the updated record is committed the Form_AfterUpdate event fires.