Run SQL on field value change using previous value and new value

1
votes

I'm new to VBA and Access and have inherited a database that needs an improvement.

I have a form in Access that shows a table for editing: enter image description here

When the labour rate value in the table is changed I need to run the following query to update another table:

update finishedproduct set labourrate = newvalue where labourrate = oldvalue;

(Yes the design of this database is bad.)

  • Which event can I handle to react to the labour rate changing?

  • How can I get the previous value of the field and then the new value of the field?

  • Something I don't know: When the user changes the labour rate value in this grid is the table updated immediately?

1
ms-accessvba

1 Answers

3
votes

You can use the BeforeUpdate event of the form and OldValue has the previous value (Value is the new value):

Private Sub Form_BeforeUpdate(Cancel As Integer)
    MsgBox Me.txtLabourRate.OldValue
    MsgBox Me.txtLabourRate.Value
End Sub

This happens just before the record is updated. You cannot use the AfterUpdate event of the Form because the OldValue will be the same as the new value.

BeforeUpdate happens immediately before the record is updated. The record will be updated - unless the Cancel argument is specifically set to True. There is the Current event for the Form but this happens after the record has been updated and Old/Value will reflect that of the current row - the row they have moved to.

It is possible to use the AfterUpdate event of the form, but there is no need: use BeforeUpdate. In order to use AfterUpdate you would need to use the Current event to store the old/current value in a variable.

When the field is changed the record is not updated immediately, but the whole record is updated when they move away from the current row (record).