Creating AuditTrail - how to handle empty values

[kfschaefer]

I need to keep an audit trail of certain fields on different forms that will on before update of a field capture the original value - compare to new value if different create an audit trail of both the original value and the new values for different records on the Audit Trail log. I need to capture the changes of the Work Statement No(WSNo) and Measurement No (MN).

If the MN is being assigned to a current WSNo then it will be considered an "Add".
If the MN is being assigned to a new WSNo then it will be considered an "Add".
If the WSNo is replacing a current MN with a new MN then it will be considered an "Add", however the previous value should be captured as a "Delete"
If the WSNo has MN's assigned and the WSNo is deleted then I need to create a list of those MN's and set them as Deletes in the Audit Trail Log, along with noting that the WSNO is a "Delete".

I hope I made this not to difficult to understand. I have looked at the "How to create an Audit trail at MS website - not sure how to modify it to be applicable to my needs since I only want to capture certain fields.

I found the example at Techrepublic website and I am attempting to modify for my needs and still need some assistance, please.

http://articles.techrepublic.com.com/5100-...11-6166807.html

after modifying the following code - I have run into an issue with handling the before value when it is NULL or empty. I attempted to use the NZ function but it still fails.

Option Compare Database
Option Explicit
Const cDQ As String = """"
Sub AuditTrail(frm As Form)
  '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
     For Each ctl In frm.Controls
        If ctl.Tag = "Audit" Then
            varBefore = Nz(ctl.oldvalue)
 Debug.Print varBefore
           varAfter = Nz(ctl.Value)
Debug.Print varAfter
            If varAfter <> varBefore Then
                strControlName = Nz(ctl.Name)
Debug.Print ctl.Name
                'Build INSERT INTO statement.
                strSQL = "INSERT INTO " _
                   & "Audit (EditDate, User, ApNo, WSNO, MeasNO, " _
                   & " SourceField, BeforeValue, AfterValue) " _
                   & "VALUES (Now()," _
                   & cDQ & Environ("username") & cDQ & ", " _
                   & cDQ & GetgApNo() & cDQ & ", " _
                   & cDQ & GetgWSNO() & cDQ & ", " _
                   & cDQ & GetgMeasNo() & cDQ & ", " _
                   & cDQ & strControlName & cDQ & ", " _
                   & cDQ & varBefore & cDQ & ", " _
                   & cDQ & varAfter & cDQ & ")"
                DoCmd.RunSQL strSQL
Debug.Print strSQL
            End If
        End If
    Next ctl
    Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error GoTo Form_BeforeUpdate_Error

    gApNo = Me.ApNo
    gWSNO = Me.WS_No
    Call AuditTrail(Me)
   

   On Error GoTo 0
   Exit Sub

Form_BeforeUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_FE_WS_Main"
End Sub

 

[ghudson]

Instead, try testing if the before value is Nz, if true then do not audit that field for that record.

 

[kfschaefer]

what do you mean testing - please explain?

 

[kfschaefer]

I did as you suggested, however,

the query runs correctly, it is only the ctl.oldvalue that seems to be causing the problem.
After the error appears the Audit table has been updated correctly.

I even tried running the code w/o error messaging and a default msg still appeared stating that same issue.


Sub AuditTrail(frm As Form)
'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 AuditTrail_Error

For Each ctl In frm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.oldvalue, "empty") = True Then
Exit Sub
Else
varBefore = ctl.oldvalue
End If
varAfter = Nz(ctl.Value, "empty")
If varAfter <> varBefore Then
strControlName = Nz(ctl.Name)
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, ApNo, WSNO, MeasNO, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & GetgApNo() & cDQ & ", " _
& cDQ & GetgWSNO() & cDQ & ", " _
& cDQ & GetgMeasNo() & cDQ & ", " _
& cDQ & strControlName & cDQ & ", " _
& cDQ & Nz(varBefore, "Empty") & cDQ & ", " _
& cDQ & Nz(varAfter, "Empty") & cDQ & ")"
DoCmd.RunSQL strSQL
End If
End If
Next ctl
Set ctl = Nothing
Exit Sub

On Error GoTo 0
Exit Sub

AuditTrail_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AuditTrail of Module basAuditTrail"

End Sub

 

[GrahamMandeno]

Hi kfschaefer

I'm not sure what you are intending here:

If Nz(ctl.oldvalue, "empty") = True Then
    Exit Sub

Do you want to exit your sub if ctl.OldValue is null? If so, try this:

If IsNull(ctl.oldvalue) Then
    Exit Sub

However, I don't think this is really your intention. Say you have five controls with the "Audit" tag, all with values except the second (which is null). You edit the record and enter new values into all five fields. As you go around the loop, the change to the first field will be logged, but when you get to the second control your procedure will exit and the other four changes will not be logged.

I'm guessing this will come closer to what you want to achieve:

  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
     For Each ctl In frm.Controls
        If ctl.Tag = "Audit" Then
           varBefore = ctl.OldValue
           varAfter = ctl.Value
           ' you cannot compare if one value is Null
            If Nz(varAfter,0) <> Nz(varBefore,0) Then
                strControlName = ctl.Name
                'Build INSERT INTO statement.
                strSQL = "INSERT INTO " _
                   & "Audit (EditDate, User, ApNo, WSNO, MeasNO, " _
                   & " SourceField, BeforeValue, AfterValue) " _
                   & "VALUES (Now()," _
                   & cDQ & Environ("username") & cDQ & ", " _
                   & cDQ & GetgApNo() & cDQ & ", " _
                   & cDQ & GetgWSNO() & cDQ & ", " _
                   & cDQ & GetgMeasNo() & cDQ & ", " _
                   & cDQ & strControlName & cDQ & ", " _
                   & cDQ & Nz(varBefore, "<empty>") & cDQ & ", " _
                   & cDQ & Nz(varAfter, "<empty>") & cDQ & ")"
                CurrentDb.Execute strSQL, dbFailOnError
            End If
        End If
    Next ctl
    Set ctl = Nothing

Note that I am also using CurrentDb.Execute instead of RunSQL. That way you won't get those pesky confirmation warning messages.

Good luck!
--
Graham

 

[kfschaefer]

I finally found the real problem - turns out that I had the tag set to Audit on a label by mistake. Once I removed it everything started working correctly.

Thanks for the great assist.

Karen

 

[The_Doc_Man]

Ah, a good catch.

Just FYI (and also for anyone new to Access who read this...).

The underlying problem is that a label doesn't have an old value - or, for that matter, any value at all - so you get an Access error claiming the .OldValue property doesn't exist. Since the audit code looked for the old value, the error cropped up. Other things that are like that include lines, rectangles, images, and in general anything that cannot be bound to an underlying recordsource.

 

[GrahamMandeno]

Hi Karen

I'm glad you've got it working

Further to The_Doc_Man's comments, there are a couple of ways to prevent this kind of error creeping into your code:

First, you can check the type of control using the ControlType property - for example:

    If ctl.ControlType = acTextBox Then
        MsgBox ctl.Name & " is a text box"
    End If

Secondly, you can use the following function to ascertain whether a control is bound (there's no point auditing an unbound contol):

Public Function IsControlBound( ctl As Control) As Boolean
Dim sCtlSrc as String
On Error Resume Next
  sCtlSrc = ctl.ControlSource
  If Err Then ' control has no ControlSource
    Err.Clear
    IsControlBound = False
  Else
    IsControlBound = (Len(sCtlSrc) <> 0 and Left(sCtlSrc, 1) <> "=") 
  End If
End Function

Finally, Allen Browne has an interesting solution to auditing on his website here:
http://allenbrowne.com/appaudit.html

Do make sure that you get rid of the Exit Sub from your loop, so that all the auditable controls get checked.

Good luck!
--
Graham

 

[MarkK]

What I like to do too when I know I need to work with a specific subset of controls on a form, rather than iterate throught the whole collection ...

For Each ctl In frm.Controls
[COLOR="Green"]  'do stuff here[/COLOR]
Next

... is create an array when the form opens, like ....

private vMyControls as variant

private sub form_open(cancel as integer)
  vMyControls = Array(me.control1, me.control2, me.someothercontrol)
end sub

... and then I can iterate thru that subset which has been explicitly defined in the code ...

dim var as variant
for each var in vMyControls
[COLOR="Green"]  'do stuff here[/COLOR]
next

 

[boblarson]

private sub form_open(cancel as integer)
vMyControls = Array(me.control1, me.control2, me.someothercontrol)
end sub[/code]
... and then I can iterate thru that subset which has been explicitly defined in the code ...

dim var as variant
for each var in vMyControls
[COLOR=green] 'do stuff here[/COLOR]
next

You should store the array definition in a table and load it from there. That way if anything changes, no coding changes are necessary; just an update to the table.

 

[MarkK]

Yeah, you could do that but it seems excessive to me. I just do this as a little shorthand tweak to avoid repeatedly iterating thru the whole controls collection of the form. Also, if the .Tag property determines controls to include this info is hard to find. Likewise if I have to consult a table somewhere, same thing.
I like this because it's such a self-documenting explicit list of participating controls.
IMO

 

[boblarson]

You may say so, but I talk from experience. Try to make as much of your stuff reusable and maintenance free. It will save work for you and anyone that comes after you in the long run.

You really should make code as good as your normalization. Make it so that you need not modify code if anything changes. It is a good thing and I've come to see that at my place of employment. They do it that way and it makes life SOOOOOOO much easier. But, just like normalization you can choose to to it or not but the benefits are tangible.