Problem with Audit trail module

[jtm013]

Hello,



I have a database which is set up to record changes to an audit trail.
I have had no problems with this feature and it has been in use for approximately 6 months now.
The methodology is simple a tag of "Audit" is placed on each field I want changes record for, in the before update event I call the audit procedure and it writes the old and new value to an audit trail table.

Recently this has started throwing an overflow error and I cannot figure out why.
I have listed the code below and made red/bold the code segment which access tells me is producing the overflow error.

Sub AuditChanges(IDField 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
    Dim strCompID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    strCompID = Environ("ComputerName")
    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
                    ![ComputerName] = strCompID
                    ![FormName] = Screen.ActiveForm.Name
                    ![recordID] = Screen.ActiveForm.Controls(IDField).Value
                    ![FieldName] = ctl.ControlSource
                    ![OldValue] = ctl.OldValue
                    ![NewValue] = ctl.Value
                    .Update
                End With
            End If
        End If
    Next ctl
'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

Any and all assistance is appreciated.

[JoeM]

Try explicitly using the second argument of the Nz function. I think it might be choking on a situation where both are Null.
See: http://www.techonthenet.com/access/f...dvanced/nz.php