I have a form which is passing the changed value to an audit trail. it typically works fine but it seams when there is a "" or ( ) or : in the value itself it will break the audit trail and not record the change.
can someone please help me understand how to fix this?
Below is the audit trail code:
Const cDQ As String = """"
Sub AuditTrail3(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.
Select Case ctl.ControlType
Case acTextBox, acCheckBox, acComboBox
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, Company_Name, Form_Name) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ", " _
& cDQ & frm.Parent!Company_Name & cDQ & ", " _
& cDQ & frm.Parent.Name & cDQ & ")"
'View evaluated statement in Immediate window.
Set ctl = Nothing
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"