The query your form is built on seems fine.
What are the circumstances that you want to trigger this update on?
If it is related specifically to the data on the form, then an update query is not generally the best approach. I would put some code in a
Form_BeforeUpdate event procedure, unless you'd like this to be visible to the operator for review before the record is saved, in which case an
AfterUpdate event procedure of the dependent control would be more suitable. Frankly, as this is an adjustment rather than a simple creation, the latter is no longer an option. This must only be done immediately prior to saving the record, otherwise it's perfectly possible to apply the same change multiple times. This would clearly cause problems in this scenario.
- Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Me.QuantityOnHand = Me.QuantityOnHand - Me.Quantity
-
End sub