Audit Trail "Type mismatch" error

[rrowsam]

Hey everyone. I attempted to use this https://www.fontstuff.com/access/acctut21.htm to create a audit trail in my database.

However, it will only capture if I delete a record, for all other changes it gives me a "Type mismatch" error.



Anyone have any ideas on what this means, and how to correct it?

Thanks!!

[June7]

Are you saying the example database doesn't work? Or code in your db does not work?

What line triggers error?

[rrowsam]

The example database works fine.

I can't tell what line is triggering the error. I'll paste the code I'm using. The error pops up whenever I edit any field on my form.

Would it be helpful to attache my DB?

Thanks.

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

[June7]

Step debug.

Attach db if you want. I can look at it.

[rrowsam]

Database attached.

Deleted most items and changed most of the info to gibberish, but still getting the same error.

[June7]

Which form - Task List? It opens to DataEntry Yes, only new record entry. What steps should I take to replicate issue?

[orange]

rrowsam,

Read the thread here for more info on the Audit trail program you are working with.
Good luck.

[rrowsam]

Which form - Task List? It opens to DataEntry Yes, only new record entry. What steps should I take to replicate issue?

Yes, the Task List Form.

If I change anything (such as assessment type or status) than click out of the row, the error pops up.

Thanks!

[June7]

Form opens to new record row. There is nothing to change. I try the search box with value BFSWERG and this causes all records to display.

I disable the On Error GoTo, run code. It breaks on line "If Nz(ctl.Value) <> Nz(ctl.OldValue) Then". I place a Debug.Print ctl.Name before that line and find it breaks on: "Assigned To".

I change that control's tag to "xAudit" so it is ignored and don't get error. So why does this control cause error? I am stumped.
I also tried Debug.Print for that control Value and OldValue and each of those cause the error. Very odd. None of the other comboboxes have issue.


Unrelated, but advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.