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?

score 1 · Answer 1 · answered Jul 28 '14 at 18:04

1

Forms and controls have Order of Events :

Similarly, when you close a form, the following sequence of events occurs:

Exit (control) → LostFocus (control) → Unload (form) → Deactivate (form) → Close (form)

If you've changed data in a control, the BeforeUpdate and AfterUpdate events for both the control and the form occur before the Exit event for the control.

You may wish to read http://support.microsoft.com/kb/197592

answered Jul 28 '14 at 18:04

Fionnuala

90,370
7
114
152

I understand the order of events, the problem is that since the code is sorting through all the controls corresponding to a record, it is finding the values that have already been recorded and recording them again. I need a way to get the code to only record the final state the controls are left in when the user leaves the record. I have tried to use the `OnRecordExit` event but I don't think it is supported in 2007. Following MSDN's example I used `Forms![PartsDatabaseX]![RepsSubformX].Form.OnRecordExit = "=ToTracking()"` – 110SidedHexagon Jul 29 '14 at 14:37
If you use the form after update event, I do not see why you should get duplicate values. The reason I posted the above, is that I suspected that you had the wrong event. I also posted a link that may help. – Fionnuala Jul 29 '14 at 15:23
You weren't wrong about the `AfterUpdate` event triggering at the correct time. The issue is that the `Ctl.OldValue` isn't updated during this event so the current and new value will always be the same. And the link you posted also uses the `BeforeUpdate` event. – 110SidedHexagon Jul 29 '14 at 18:28

score 0 · Accepted Answer · answered Jul 29 '14 at 19:18

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

How to detect what control triggers BeforeUpdate event in Access 2007

2 Answers2