Automatically record current date and time when a specific field is modified

[jmbreland]

I would like for my Access 2007 database to automatically record the date/time when the data in a SPECIFIC field is modified (not when any data in the record changes).



Here is a simplified explanation of my scenario. This database keeps track of cases in my office. A data entry form named "[Case Status Update]" feeds a table named "Cases." The form includes text boxes that correspond to the fields in the table. Three of these are: "Case_Notes," "Case_Status," and "Date_Status_Change."

I would like for the "Date_Status_Change" field to automatically reflect the current date/time whenever the data in the "Case_Status" field changes. I do not want the date/time to change when information in the "Case_Notes" field is modified.

The following code will enter the date/time for a change anywhere in the record:

Private
Sub Form_BeforeUpdate(Cancel As Integer)
Me![Date_Status_Change].Value = Now()
End Sub

But I want to limit it so that it dates changes ONLY in the “Case_Status” field. What code would accomplish this?

[John_G]

Put the code in the After Update event of the Case_Status field.

[jmbreland]

Got it. This works insofar as I decided I only want a date entry when the status changes to "complete."

Private Sub Status_AfterUpdate()
If Me.Case_Status = "Complete" Then
Me.Date_Status_Change = Now()
End If
End Sub