I used an audit trail from a previous post to track changes made to a table via a form but I am getting an error when I try to use the second code in the actual form. I get a type mismatch error on the Call line and I don't understand why.
Here is the module:
Then I entered this in the "Before Update" section of my form SBAR (Event Procedure). Here is where I get the mismatch error.
Table1: SBAR
Table2: Audit
Form: SBAR
Module: basAuditTrail
Primary Key: SBARNumber
I am very new to access and just finished my level two class so please bear with me.
Here is the module:
Code:
Option Compare Database
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.
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.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End SubThen I entered this in the "Before Update" section of my form SBAR (Event Procedure). Here is where I get the mismatch error.
Code:
Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call basAuditTrail.AuditTrail(Me, SBARNumber)
End SubTable1: SBAR
Table2: Audit
Form: SBAR
Module: basAuditTrail
Primary Key: SBARNumber
I am very new to access and just finished my level two class so please bear with me.
Last edited:
