auto insert date upon change of a control

[REGREGL]

Hey all....

I have a form (in continuous form view) which has a combo box for order
status. I would like to automatically create a date whenever someone changes
the status of an order. So, I imagine I would create a seperate table
called, say...update status date a which contains 2 fileds (Order Number and
Status Change Date). How do I get the Order Number and Status Change Date
record into the new table whenever the staus is changed?


Thanks in advance!!!

 

[Allen Browne]

Use the events of the form to insert a record into your logging table.

You will need to use Form_BeforeUpdate and Form_AfterUpdate in combination.
Form_BeforeUpdate can fire even when a record is not saved (e.g. if it is
discovered that a required field is not filled in), so it is not reliable
on its own. Form_AfterUpdate is reliable, but at that stage you don't have
access to the previous data, so you can't tell if the status changed or not.
Therefore you will need to:
1. Declare a form-level variable (delcared in the General Declarations
section, at the top of the form's module):
Private mbStatusChanged As Boolean

2. Set the value of this variable in Form_BeforeUpdate, e.g.:
If (Me.NewRecord) OR (Me.[Status] = Me.[Status].OldValue) Then
mbStatusChanged = False
Else
mbStatusChanged = True
End If

3. Write the logging record in Form_AfterUpdate, e.g.:
Dim strSql As String
If mbStatusChanged Then
mbStatusChanged = False
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End If

This doesn't log deletions, of course. If you need to do that too, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html