Audit Trail That Captures Change To Null

[barbarao]

Hi, I found code on the Internet for creating an audit log. Works great except it does not record if you edit a value to become blank. I tried a few things and can't get it to work. Can anyone help out? Thanks in advance.

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 = Environ("USERNAME")
    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
                        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
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                .Update
            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 
 

[jimatqsi]

barbaro,
This code looks pretty good. You don't really tell us anything about the nature of the problem. Does it fail to make an audit record when you change any field at all to be blank? The problem is presumably occurring at

If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

but we don't have enough information to say that for sure.

Do you know how to run debug in the code? If you go to that line and hit the F9 key you'll set a debug stop. Then run the code and when that line is to be executed the debugger will come up (current line highlighted in yellow). You can then get variable values by pointing at a variable or in the immediate window (control/g) - type ?variablename to get the value. F8 to move to the next line in the code (or F10, depending on what version you're in). Use the Debug menu to guide you and look at debugging in Help. Educate yourself about debug, try a few things and come back with some more specific questions.

Jim

[zmbd]

Actually,
I recommend against using the [F9] stop point.
It has been documented to cause issues with the compiler.
Instead, insert the STOP command in-between lines 7 and 8 (just before the first SET command).
From there proceed as jimatqsi has suggested.

Please take a look at the trouble shooting section here:
> Before Posting (VBA or SQL) Code

AND take a look at the trouble shooting articles in the Microsoft Access / VBA Insights Sitemap listing (open the page and then use <ctrl><f> to open the page search to find "debugging in vba" or look for 129 thru 139...

[barbarao]

Hi,

The code works fine when I change any field from "Value 1" to "Value 2". The change gets recorded properly in the audit table. If I change "Value 2" to be null (blank), the change is not recorded in the table. I believe it is the line jimatqsi suggested and I have tried

If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then 

but that didn't do anything. I'm having a hard time doing anything with debug as it won't let me run the code for some reason. Any other thoughts?

[zmbd]

How are you calling the code?

[jimatqsi]

What do you mean by "it won't let me run the code "? Are you able to set the stop point with F9 (line of code turns brown)? Then when you run the code (open the form if it's not open, make a change to a field, do whatever event is required to call Sub AuditChanges) it should start the process but stop with the line highlighted in yellow. At what point are you prevented from running your code?

Jim

[barbarao]

I can make the stop point and have done so. I then keep the code open but minized and on the form, remove a value from a field and save the record. I don't see anything that is happening with the code. Sorry but I am self taught.

[jimatqsi]

Perhaps if you switch windows to the code you'll see it is waiting at a yellow highlighted line. Or else your Sub is not being called in this instance.

If the program is not sitting at the break point then we have to find out what calls your auditing logic. If you can tell us that (preferably post the code that calls AuditChanges). It could be from within the code running when you save a record.

Do the reading that zmbd listed for you. You'll learn a lot and be glad for it.

Jim

[barbarao]

When I try what you suggested, I do not see a yellow line in the code. The code is called from the BeforeUpdate Eventof the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Call AuditChanges("StuffID", "EDIT")
End Sub

I'll be reading that tonight and really appreciate all that I learn here.

[zmbd]

barbarao,

1) Before you do anything else to your project, please, please, please, please complete all of the trouble shooting and configuration steps given in: > Before Posting (VBA or SQL) Code. If you will not do this, then we will be chasing our tails for a 100 posts!

2) Please do !not! use the [F9] method of setting the stop/break point. I understand that many people do; however, it somtimes fails and if certain conditions are met, the VBA code can be corrupted. If you have used this method, please go back into your code and clear any of those "red" dots.

2a) Place the STOP command as the very FIRST command in your "Form_BeforeUpd ate" so that you get something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'>> Debug Stop
   STOP
'>>
   Call AuditChanges("StuffID", "EDIT")
End Sub

if this does not cause the code to enter the debug mode and highlight the call to the AuditChanges then we need to start checking your program settings... once again, if you will follow all of the provisions in the > Before Posting (VBA or SQL) Code link for trouble shooting most of this will fix itself.