How to detect what control triggers BeforeUpdate event in Access 2007

Question

I have an audit trail that uses the BeforeUpdate event to track changes made on a subform using the following code:

`Dim USR As String
Dim TS As Date
Dim Connection As ADODB.Connection
Dim RecordSet As ADODB.RecordSet
Dim Ctl As Control
MsgBox "Here!"
Set Connection = CurrentProject.Connection
Set RecordSet = New ADODB.RecordSet
If Forms![PartsDatabaseX]![RepsSubformX].Visible = True Then
    For Each Ctl In Screen.ActiveForm.RepsSubformX.Form.Controls
        If Ctl.Tag = "Track" Then
            If Nz(Ctl.Value) <> Nz(Ctl.OldValue) Then
            SaveToken = True
                With RecordSet
                    .AddNew
                    ![Part Number] = Screen.ActiveForm.RepsSubformX.Form.Controls("[Part Nbr]").Value
                    ![Record Identifier] = Screen.ActiveForm.RepsSubformX.Form.Controls("[Part Nbr]").Value & Screen.ActiveForm.RepsSubformX.Form.Controls("[Supplier Name]").Value
                    ![Rep] = USR
                    ![Time Stamp] = TS
                    ![Change Point] = Ctl.ControlSource
                    ![Change From] = Ctl.OldValue
                    ![Change To] = Ctl.Value
                    .Update
                End With
            End If
        End If
    Next Ctl
End If`

The problem I am having is that is the user makes two changes there are three things recorded in my change history table - the first change to the record twice and the second change to the record once (this trend continues as long as the user never leaves the record). What I would like to do is be able to identify the control that triggered the BeforeUpdate event and pass it to the code above so it can check if only the control that triggered the BeforeUpdate event is different and skip the others that have already been logged. Alternatively, is there a way to prevent Access from seeing logged changes as new?

Answer 1

The answer has been staring me in the face the whole time... When I assign the BeforeUpdate event to each control, I can make it pass a variable to the function it calls to tell me the program what sent it:

Forms![PartsDatabaseX]![RepsSubformX].Form![Pack Rank].BeforeUpdate = "=ToTracking(""Pack Rank"")"

After that, it's a simple matter of adding an and statement when checking for changed values so it only captures the change that set off the BeforeUpdate event like so:

If Nz(Ctl.Value) <> Nz(Ctl.OldValue) And Ctl.ControlSource = NameOfTrigger Then

'Record Values

End if