Audit trail in separate fields possible ?

[jamie_b]

Hello,

In ACC2000, I am using the Module from this page...
http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
... to keep an audit trail of the record.
So, the table has a field named "fAuditTrail" and on my form(s), in that field's BeforeUpdate event, it calls the "AuditTrail()" module.

This all works perfectly.

The module displays the current (windows)Username, Date(now) and also which data(fields) have been added or changed.

For statistical/reporting purposes, I was hoping to be able to change the Audit Trail Module, so that the Username, Date, and Data changed, is inserted into SEPARATE FIELDS on the form.

So in my table I will have fields for :
AuditUser, AuditDate, AuditData
and ofcourse those same fields on the Form.

I am not sure how to go about changing the Module to do this.

I will sincerely appreciate any advice from you.

Thank you kindly.

Jamie.

 

[dbDamo]

I use the audit trail in this link and think it could be what you're after:-

http://articles.techrepublic.com.com...1-6166807.html

I have had to edit it to ensuring null value changes are captured (null to value & value to null)

 

[jamie_b]

I use the audit trail in this link and think it could be what you're after:-

http://articles.techrepublic.com.com...1-6166807.html

I have had to edit it to ensuring null value changes are captured (null to value & value to null)

Thank you.

I like the idea of having the AuditTrail in a separate Table. This way I can easily generate my reports on the Audit.

But that Module does not track NEW records. (only existing records that changes).

I admit to being fairly novice with this.

Can you please offer any advice on how to change the Module so that it will also track NEW Records ?

Thanks again for showing interest.

Jamie.

 

[dbDamo]

Like I said I had to edit it to capture null value changes

Here is the code I use:-

Sub AuditTrail2(frm As Form, RecordID As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
 
   Select Case .ControlType
 
   [B]Case acTextBox[/B]

[B]      If IsNull(.Value) Then[/B]
[B]        If Not IsNull(.OldValue) Then[/B]
[B]        varBefore = .OldValue[/B]
[B]        varAfter = .Value[/B]
[B]        strControlName = .Name[/B]
[B]        'Build INSERT INTO statement.[/B]
[B]        strSQL = "INSERT INTO " _[/B]
[B]           & "IncidentAudit (EditDate, User, RecordID, SourceTable, " _[/B]
[B]           & " SourceField, BeforeValue, AfterValue) " _[/B]
[B]           & " VALUES (Now()," _[/B]
[B]           & cDQ & Environ("username") & cDQ & ", " _[/B]
[B]           & cDQ & RecordID.Value & cDQ & ", " _[/B]
[B]           & cDQ & frm.RecordSource & cDQ & ", " _[/B]
[B]           & cDQ & .Name & cDQ & ", " _[/B]
[B]           & cDQ & varBefore & cDQ & ", " _[/B]
[B]           & cDQ & varAfter & cDQ & ")"[/B]
[B]        'View evaluated statement in Immediate window.[/B]
[B]        Debug.Print strSQL[/B]
[B]        DoCmd.SetWarnings False[/B]
[B]        DoCmd.RunSQL strSQL[/B]
[B]        DoCmd.SetWarnings True[/B]
[B]      End If[/B]
[B]      End If[/B]

[B]       If Not IsNull(.Value) Then[/B]
[B]        If IsNull(.OldValue) Then[/B]
[B]        varBefore = .OldValue[/B]
[B]        varAfter = .Value[/B]
[B]        strControlName = .Name[/B]
[B]        'Build INSERT INTO statement.[/B]
[B]        strSQL = "INSERT INTO " _[/B]
[B]           & "IncidentAudit (EditDate, User, RecordID, SourceTable, " _[/B]
[B]           & " SourceField, BeforeValue, AfterValue) " _[/B]
[B]           & " VALUES (Now()," _[/B]
[B]           & cDQ & Environ("username") & cDQ & ", " _[/B]
[B]           & cDQ & RecordID.Value & cDQ & ", " _[/B]
[B]           & cDQ & frm.RecordSource & cDQ & ", " _[/B]
[B]           & cDQ & .Name & cDQ & ", " _[/B]
[B]           & cDQ & varBefore & cDQ & ", " _[/B]
[B]           & cDQ & varAfter & cDQ & ")"[/B]
[B]        'View evaluated statement in Immediate window.[/B]
[B]        Debug.Print strSQL[/B]
[B]        DoCmd.SetWarnings False[/B]
[B]        DoCmd.RunSQL strSQL[/B]
[B]        DoCmd.SetWarnings True[/B]
[B]      End If[/B]
[B]      End If[/B]

[B]      If .Value <> .OldValue Then[/B]
[B]        varBefore = .OldValue[/B]
[B]        varAfter = .Value[/B]
[B]        strControlName = .Name[/B]
[B]        'Build INSERT INTO statement.[/B]
[B]        strSQL = "INSERT INTO " _[/B]
[B]           & "IncidentAudit (EditDate, User, RecordID, SourceTable, " _[/B]
[B]           & " SourceField, BeforeValue, AfterValue) " _[/B]
[B]           & " VALUES (Now()," _[/B]
[B]           & cDQ & Environ("username") & cDQ & ", " _[/B]
[B]           & cDQ & RecordID.Value & cDQ & ", " _[/B]
[B]           & cDQ & frm.RecordSource & cDQ & ", " _[/B]
[B]           & cDQ & .Name & cDQ & ", " _[/B]
[B]           & cDQ & varBefore & cDQ & ", " _[/B]
[B]           & cDQ & varAfter & cDQ & ")"[/B]
[B]        'View evaluated statement in Immediate window.[/B]
[B]        Debug.Print strSQL[/B]
[B]        DoCmd.SetWarnings False[/B]
[B]        DoCmd.RunSQL strSQL[/B]
[B]        DoCmd.SetWarnings True[/B]
[B]      End If[/B]
End Select
 
    End With
  Next
  Set ctl = Nothing
  Exit Sub
ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

The code in bold is then repeated for each control type