Identify when controls have been modified 2

[bmc1234]

I have a form with about 40 controls on it in the form of text boxes, combo boxes, and check boxes. In the afterupdate event of the form I want to identify which fields have been changed and write the new values of those fields to a table that keeps track of changes. I don't want to have to put code in an event of every control individually because I want to have all the code in one place. Is there any way to identify whether a particular control has been modified? I figure it would be something like the Dirty property, but that is only valid for the record as a whole, not individual fields.

 

[ZmrAbdulla]

a starting point Form - looping thru the controls faq702-5010

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[imterpsfan3]

I created a table called tblAuditTrail with fields RecordID, ControlName, OldValue, NewValue then added this code. I would name your controls as the same name as your field name in the database.

Option Compare Database
Option Explicit
Dim ctl As Control
Private Sub Form_BeforeUpdate(Cancel As Integer)
      
    Dim strSQL
   
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            If ctl.OldValue <> ctl.Value Then
            strSQL = "INSERT INTO tblAuditTrail(RecordID, ControlName, OldValue, NewValue) " & _
            "VALUES (" & ID & ",'" & ctl.Name & "', '" & ctl.OldValue & "','" & ctl.Value & "')"
                CurrentProject.Connection.Execute strSQL
            End If
        End If
    Next ctl
    
    Set ctl = Nothing
End Sub