Audit Trail Code Issue

[PaulA]

Hi, all -

I found code online for creating an Access audit trail which seems pretty good (conceptually) but I am having a basic problem: the code isn't recognizing data edits.

First of all, I am running a 2003 database through Access 2007 (which may be becoming 2013 soon).

The code (at least the first part where the trouble is) is:

Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = CurrentUser
Debug.Print strUserID
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
Debug.Print "same to them"
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![recordid] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If

The problem is with the "If nz(ctlvalue) <> nz(ctl.oldvalue) line.

I did a debug.print test and it doesn't recognize the changes in the data.

Any help will be appreciated.

Paul

 

[Acropolis]

try

 If Not nz(ctl.Value) = nz(ctl.OldValue) Then

 

[jdraw]

Paul,

I reformatted your code with Smart Indenter to improve readability.

Sub AuditChanges(IDField As String, UserAction As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = CurrentUser
    Debug.Print strUserID
    Select Case UserAction
    Case "EDIT"
        For Each ctl In Screen.ActiveForm.Controls
            If ctl.Tag = "Audit" Then
                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    Debug.Print "same to them"
                    With rst
                        .AddNew
                        ![DateTime] = datTimeCheck
                        ![UserName] = strUserID
                        ![FormName] = Screen.ActiveForm.name
                        ![Action] = UserAction
                        ![recordid] = Screen.ActiveForm.Controls(IDField).Value
                        ![fieldName] = ctl.ControlSource
                        ![OldValue] = ctl.OldValue
                        ![newValue] = ctl.Value
                        .Update
                    End With
                End If
            End If

 

[PaulA]

Thanks, JDraw and Acropolis.

Unfortunately, Acropolis' suggestion didn't work.

 

[jdraw]

Seems to me this line
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
is saying if the Old and New Values are different then set up to write to audit.

You could try some other step debugging/debug.print
I'd start with putting a breakpoint on the line in question, then run the program to breakpoint, then using the Locals window and immediate window do some investigation of values in Local window. You can also also do things like

?ctl.name
?ctl.value
?ctl.OldValue
?ctl.ControlType

while the code is in breakpoint. Somethings may not have values, but that is the sort of approach.
You might also put a default on the NZ()

eg: Nz(ctl.Value,"") <> Nz(ctl.OldValue,"") 'zero length string

Finding out which control will be a big step.

Good luck. Let us know what you find.

 

[PaulA]

Thanks, JDraw.

I had done some debug.print testing and they were always bypassed - never got anything in the immediate window which is why I assumed the code wasn't picking up the changes. I tried your other suggestion and am basically getting the same thing.

I like how this auditing method works and would like to use it if I can figure out why it isn't picking up the changes.

 

[jdraw]

I noted there is no NEXT command to complete your For Loop.

Is there more code in your routine? If so , could you post it all.
Or if you have a small test database using this audit routine, could you post that?

 

[PaulA]

Here is the complete routine:

Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = CurrentUser
Debug.Print strUserID
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
Debug.Print "same to them"
Debug.Print ctl.Name
Debug.Print ctl.Value
Debug.Print ctl.OldValue
Debug.Print ctl.ControlType
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![recordid] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Debug.Print "Same to us"
Next ctl

Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![recordid] = Screen.ActiveForm.Controls(IDField).Value
.Update
Debug.Print "Oh Yeah"
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub

 

[PaulA]

I realize I don't know how to use "Smart Identifier" to use indents. If someone would tell me how I'll redo it.

Thanks.

 

[jdraw]

Smart Indenter is a free utility. I have used it with 200, 2003 and 2010.

You should also get the free MZToolsForVBA --excellent and free.

As for Audit Trail/Log, here are some links to Allen Browne's articles.
http://allenbrowne.com/AppAudit.html
http://allenbrowne.com/AppAuditCode.html

 

[PaulA]

Sorry for the delay in my response, jdraw.

I'll check them out.

Paul