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
'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.
strSQL = "INSERT INTO " _
& "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.
Set ctl = Nothing
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
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)
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."