All using access 2003. I adopted code for an audit trail and I can't get it to work. Below is the code:

Const cDQ As String = """"

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

'Get changed values.

For Each ctl In frm.Controls

With ctl

'Avoid labels and other controls with Value property.

If .ControlType = acTextBox Then

If .Value <> .OldValue Then

varBefore = .OldValue

varAfter = .Value

strControlName = .Name  'Build INSERT INTO statement.


& "Audit (EditDate, User, RecordID, SourceTable, " _

& " SourceField, BeforeValue, AfterValue) " _

& "VALUES (Now()," _

& cDQ & Environ("username") & cDQ & ", " _

& cDQ & recordid.Value & cDQ & ", " _

& cDQ & frm.RecordSource & cDQ & ", " _

& cDQ & .Name & cDQ & ", " _

& cDQ & varBefore & cDQ & ", " _

& cDQ & varAfter & cDQ & ")"        'View evaluated statement in Immediate window.

Debug.Print strSQL

DoCmd.SetWarnings False

DoCmd****nSQL strSQL

DoCmd.SetWarnings True

End If

End If

End With


Set ctl = Nothing

Exit Sub


MsgBox Err.Description & vbNewLine _

& Err.Number, vbOKOnly, "Error"

End Sub

I created the table called audit accordingly and calling it from my form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call AuditTrail(Me, NUM_TRACKINGID)

End Sub
It says to use the pk of the form. When I do; it gives me an type mismatch debug error. I thought it was because the table field "recordid" in the audit table is text not number. But when I change this field to number I get a error " this operation is not allowed."

Can anyone help me with this code please?