Presumably you have:
- A form for entering the order, with a subform for entering the line items
of the order - similar to the Orders form in Northwind.
- FreightCharge and PurchaseOrderNumber are in the main form.
- Order Subtotal is a calculated field based on the subform items, and not
stored in the main form.
Use the AfterUpdate event procedure of the subform to calculate freight.
That's the event that runs after every record is added/edited in the
subform. Run the same code in AfterDelConfirm so it changes if you delete a
row from the subform as well.
The calculated total will not have been computed at the time
Form_AfterUpdate runs, so we will calculate it directly from the subform's
table. (This also avoids problems if there is a filter applied to the
subform.)
1.Open the subform in design view.
2. In the Properties box (View menu), make sure its title reads Form (not a
control), and set the AfterUpdate property to:
[Event Procedure]
3. Click the Build button (...) beside this. Access opens the code window.
Set up the event procedure so it looks like this:
Private Sub Form_AfterUpdate()
Dim strWhere As String
Dim curSubTotal As Currency
With Me.Parent
strWhere = "OrderID = " & Nz(![OrderID], 0)
curSubTotal = Nz(DSum("Amount", "OrderDetail", strWhere, 0)
![FreightCharge] = curSubTotal * ![PurchaseOrderNumber] / 100
.Dirty = False
End With
End Sub
4. Set the form's AfterDelConfirm property to:
[Event Procedure]
and its code like this:
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call Form_AfterUpdate
End If
End Sub
