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