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?
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