asked on

Audit trail for subforms

I have created an audit trail module to capture details of changed fields. This is based on the Microsoft example (197592) which enumerates through all the controls on the active screen (Screen.ActiveForm). This works fine for changes to the main form but doesn't work for changes to a subform even though the before update event on the subform runs the module. The Screen.ActiveForm does not seem to recognise the subform. Can anyone tell me how to cycle through the controls on the subform or provide an audit trail example which works for subforms.
Thanks

syntnx

kipper could you paste your code here

thanks

syntnx

pilby

well, if the code is being run from the subform, rather than using Screen.ActiveForm, just use "Me". that will refer to the subform. then you can refer to the fields in that subform: Me.control1, etc or:

With Me.
tempVar = control1

...

etc.

if you want it to refer to controls on a form that maybe isn't the active form, just refer to the form name explicitly:
Forms!FormName.ControlName

weston

syntnx

Kipper/weston,

I did play with the code a bit last night from the ms kba. I had the same thought as weston but still was getting an error. Give it try and let us know. Im curous myself because I think the code could be very useful.

I personally would like to take it to the next step. Maybe comapre before and after values and only write the actual changes. Ive done audit/transactions records before but in such an elegant way.

Ill try a few other things today (have to do some data imports first)

syntnx

Kipper_L

ASKER

Weston,

I've never used

With me

but will give it a try and let you know if you can then cycle through all the controls. I don't want to reference the controls by name as I want to use one peice of code for all my forms (and subforms).

Thanks

ASKER CERTIFIED SOLUTION

pilby

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pilby

oh wait, i guess you want that in the BeforeUpdate (like the MS page says), not in AfterUpdate.

Kipper_L

ASKER

Thanks for your help - it works fine now for any form.

psk1

Thanks guys. This helped me out.

Just for future viewers, here's the code I'm using (based on above modified MS code):

Function didChange(v1, v2) As Boolean

didChange = False
If IsNull(v1) And Not IsNull(v2) Then didChange = True
If Not IsNull(v1) And IsNull(v2) Then didChange = True
If didChange = True Then Exit Function
If v1 <> v2 Then didChange = True

End Function

Function recordAuditTrail(frm As Form)

Dim c As Control, xName As String
Dim sSql1 As String, sSql2 As String
Dim sSql3 As String, sSql4 As String

If frm.Name = "" Then Exit Function

sSql1 = "INSERT INTO [Reference_Audit_Trail] ( [chgType], [chgDate], [chgUser], [Row]"
sSql2 = "SELECT 'OLD', #" & Now() & "#, '" & Environ("username") & "', " & frm.Controls.Item("Row").Value

sSql3 = "INSERT INTO [Reference_Audit_Trail] ( [chgType], [chgDate], [chgUser], [Row]"
sSql4 = "SELECT 'NEW', #" & Now() & "#, '" & Environ("username") & "', " & frm.Controls.Item("Row").Value

For Each c In frm.Controls
Select Case c.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
If LCase(Left(c.Name, 7)) <> "comment" And c.ControlSource <> "Row" Then 'fields to exclude from verification
If didChange(c.Value, c.OldValue) Then
sSql1 = sSql1 & ", [" & c.ControlSource & "]"
sSql2 = sSql2 & ", '" & c.OldValue & "'"
sSql3 = sSql3 & ", [" & c.ControlSource & "]"
sSql4 = sSql4 & ", '" & c.Value & "'"
End If
End If
End Select
Next c

DoCmd.SetWarnings False
DoCmd.RunSQL sSql1 & " ) " & sSql2 & ";"
DoCmd.RunSQL sSql3 & " ) " & sSql4 & ";"
DoCmd.SetWarnings True

End Function

I had wanted to store the audit trail in a separate table, not in a field in the form on a by-record basis. I copied the structure of the table on which my form was based (so that the field names could be referred to by the controlsource of each control in the form) and added the fields chgType(text), chgUser(text), chgDate(date) to create the new Audit table. [Row] is the unique record identifier in the source table (it was Autonumber so I had to make it Number in the new Audit table).

The code is stored in a module and called from the BeforeUpdate event of the form.

Hope it's helpful to someone.