Link to home
Start Free TrialLog in
Avatar of Sanjay
SanjayFlag for United States of America

asked on

microsoft access vba coding to perform audit trail of records

I have a function developed by an expert that works very well for me when performing audit trails.  However, when I open my audit table (audAuditService), I notice that some fields have both the old and new values as null.  I do not want to include these records in my table.  Could an expert help me in modifying the code below please so that I do not include old and new values that are null.  Thanks.
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
On Error GoTo err_WriteAudit

Dim ctl As Control
Dim strSQL As String
Dim bOK As Boolean
    
bOK = False
    
DoCmd.SetWarnings False

For Each ctl In frm.Controls

    If ctl.Tag <> "safe" Then
    
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox _
            Or ctl.ControlType = acCheckBox Or ctl.ControlType = acOptionGroup Then
            
            If ctl.Value <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or IsNull(ctl.OldValue) Then

                    strSQL = "INSERT INTO audAuditService ( ID, FieldChanged, FieldName, FieldChangedFrom, FieldChangedTo, User, DateofChange, Machine, IPAddress, SourceTable) " & _
                            " SELECT " & lngID & " , " & _
                            "'" & ctl.Name & "', " & _
                            "'" & ctl.ControlSource & "', " & _
                            "'" & ctl.OldValue & "', " & _
                            "'" & ctl.Value & "', " & _
                            "'" & GetUserName_TSB & "', " & _
                            "'" & Now & "', " & _
                            "'" & fOSMachineName & "', " & _
                            "'" & GetIPAddress & "', " & _
                            "'" & frm.RecordSource & "'"
                    DoCmd.RunSQL strSQL
        
            End If
            
        End If
        
     End If

Next ctl
    
WriteAudit = bOK
    
exit_WriteAudit:
    DoCmd.SetWarnings True
    Exit Function
    
err_WriteAudit:
    MsgBox err.Description
    Resume exit_WriteAudit
    
End Function

Open in new window

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Modify this line:

            If ctl.Value <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or IsNull(ctl.OldValue) Then

to:

            If (ctl.Value <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or IsNull(ctl.OldValue)) And Not IsNull(ctl.value) Then

JimD.
actually, change it to this:

            If (ctl.Value <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or IsNull(ctl.OldValue)) And (Not IsNull(ctl.value) and Not IsNull(ctl.oldvalue)) Then

  Something may be going from a value to NULL in which case you'd want it reported.  Personally I think that if check needs to be broken up, but that should work for what you want.

JimD.

Avatar of Sanjay

ASKER

Hi Jim:

In your last post above , I typed in a value of a and b in two of my form's controls and moved away from the record, opened the audit table and did not see null values go to a and b respectively.  Then I did the reverse and deleted the values a and b and checked my audit table and still did not see the values go from a and b to null respectively.

In your second to last posting the coding works when the values go from null to some value.  However when the values go from some value to null, nothing is shown to be audited in the audit table.
Avatar of Sanjay

ASKER

never mind, now nothing is working.....I do not see any changes being tracked.  stay tuned til I figure this out.
Avatar of Sanjay

ASKER

ok, I had entered a carriage return and took it out............

so using..........

If (ctl.Value <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or IsNull(ctl.OldValue)) And Not IsNull(ctl.Value) Then

I do not see audit trail of items that have a value and then go to null.............
try this


If iif(IsNull(ctl.Value),"",ctl.Value) <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or IsNull(ctl.OldValue) Then
Avatar of Sanjay

ASKER

Hi cap:

your line above:

If iif............is that correct?
<If iif............is that correct?> Yes
Avatar of Sanjay

ASKER

Hi Cap:

Used your line verbatim, and now I am back to the same problem......
Avatar of Sanjay

ASKER

I got a boat load of records showing FieldChangedFrom null values to null values
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sanjay

ASKER

Hi Cap:

How about that being just PURRFect!!!:)  Thank you!!!!
Avatar of Sanjay

ASKER

sorry, i clicked incoorectly.  how do I correct to award points
sanjay,

are you sure you want to accept your comment as the solution?
Avatar of Sanjay

ASKER

Capricorn1 needs to be awarded all 500 pts please.
Avatar of Sanjay

ASKER

Capricorn1 needs to be awarded all 500 pts please.
sanjay,
click on Request Attention and ask for help
Avatar of Sanjay

ASKER

whew...............

Hi Cap:

So what does if...iif do in this case.......This is new for me.
IIf Function
Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

Avatar of Sanjay

ASKER

so things are going good, old values to new values are being tracked.; current values to null values are being tracked.......HOWEVER.....................null values to non-null values are not being tracked.............
test this

If iif(IsNull(ctl.Value),"",ctl.Value) <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Or ctl.OldValue & ""="" Then
Avatar of Sanjay

ASKER

hi Cap:

Confirmed.....So I went to several different records that had their respective controls set to null.  I entered values in those controls and did not see the audit trail in the corresponding table.  Only after I deleted (backspaced) these values, did I see the audit trail showing the non-null value and the new null value.  

So we still need to be able to track controls that are going from a null to a non-null value.................please.

Thanks

Sanj
Avatar of Sanjay

ASKER

will do..................stay tuned.


  I really would break that IF statement up.  Handle the situations one at a time:


  If Not IsNull(ctl.Value) or Not IsNull(ctl.OldValue) then
      If ctl.Value <> ctl.OldValue Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) then
      ' Log
      End
  End If

JimD.
Avatar of Sanjay

ASKER

Hi Cap:

Back to the original problem.........I see all the changes AND I also see the null values of the controls whose values were not changed.  I have attached an image of the audit table that shows the values of "FieldChangedFrom" and "FieldChangedTo"
test.jpg
how about this

If iif(IsNull(ctl.Value),"",ctl.Value) <> iif(IsNull(ctl.OldValue),"",ctl.OldValue) Or StrComp(ctl.Value, ctl.OldValue, vbBinaryCompare) <> 0 Then
Avatar of Sanjay

ASKER

Hi Cap and JimD

Both your methods worked wonderfully. Thank you experts!!!