Track field value changes. Please help

[metad]

Hi. I have a form with many fields. I want to track changes av 1 field to know the field value has been changed or not. I know about onchange event, but this only works when user inserts or changes the value. My form is being updated by macro/visual Basic. Is there any way to fin out if the value of a field has been changed automatically?

Thanks for any help!
metad

 

[reclusivemonkey]

Yes. You can compare .Value and .OldValue on your fields. I use the following code; by naming your fields, you can just check the relevant ones;

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Create an audit trail to track changes to cost centres
    Dim frmObjCC As Object
    Dim CCAField As String
    Dim CCAOldValue As String
    Dim CCANewValue As String
    Dim CCADateStamp As Date
    Dim CCAUserName As String
        
    For Each frmObjCC In Forms!frmCostCentres
        If Left(frmObjCC.Name, 10) = "CostCentre" Then
                If frmObjCC.Value <> frmObjCC.OldValue Then
                        CCAField = frmObjCC.Name
                        CCAOldValue = frmObjCC.OldValue
                        CCANewValue = frmObjCC.Value
                        CCADateStamp = Now()
                        CCAUserName = Environ("Username")
                        strSQL = "INSERT INTO tblCostCentresAudit (CCentAuditCostCentre, CCentAuditField, "
                        strSQL = strSQL + "CCentAuditOldValue, CCentAuditNewValue, CCentAuditDateStamp, "
                        strSQL = strSQL + "CCentAuditUserName) VALUES ("
                        strSQL = strSQL + "'" & CostCentreID.Value & "', "
                        strSQL = strSQL + "'" & CCAField & "', "
                        strSQL = strSQL + "'" & CCAOldValue & "', "
                        strSQL = strSQL + "'" & CCANewValue & "', "
                        strSQL = strSQL + "#" & CCADateStamp & "#, "
                        strSQL = strSQL + "'" & CCAUserName & "')"
                        DoCmd.RunSQL (strSQL)
                    Else
                End If
            Else
        End If
    Next frmObjCC

End Sub

 

[metad]

I do not understand!

Thank you for your help. But I do not understand what are your field names in your code. I do not need to track all changes and insert it in a table as I understand you are doing in this code. I just need to know if a single field (Called GateArea) has been changed automatically, and if so write the new value in a new field (Called GateArea2). Could you please help me?

Thank you.
metad

 

[reclusivemonkey]

Thank you for your help. But I do not understand what are your field names in your code.

I named all my text entry fields "CostCentre_Something. I simply then loop through all form objects begining with CostCentre because I am too lazy to write code for each field name ;-)

I do not need to track all changes and insert it in a table as I understand you are doing in this code. I just need to know if a single field (Called GateArea) has been changed automatically, and if so write the new value in a new field (Called GateArea2).

In that case, you need much less code;

If GateArea.Value <> GateArea.OldValue Then
        GateArea2.Value = GateArea.OldValue
    Else
EndIf

Should be enough.

Could you please help me?

Thank you.
metad

No problem, just remember to click on those scales ;-)

 

[metad]

Thant you. Wokrs perfect

I am just wondering one thing: Should the code be in BeforUpdate or AfterUpdate?

Thank you again!
metad

 

[reclusivemonkey]

BeforeUpdate