passing a value in access 2003 which has quotes, parentheses and colons

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?

Thanks

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
    With ctl
    '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.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    Case Else
   End Select
  End With
 Next
  Set ctl = Nothing
  Exit Sub


ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

Open in new window

LVL 1
HudsonMarineAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for every input that could contain quotes, you have to either escape them, and at least have quotes around the string

 & cDQ & frm.Parent!Company_Name & cDQ & ", " _

must be:
 & cDQ & replace(frm.Parent!Company_Name, """", """""") & cDQ & ", " _
0
 
HudsonMarineAuthor Commented:
thanks angelll, is this audit trail considered a function?

I will impliment your suggestion and see if it fixes the issue.

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is the quote being a string delimiter,
so you need to escape it when it occurs in the data itself ...

you have to apply this "replace" for each and every input value which is "text" data type, at least.
0
 
HudsonMarineAuthor Commented:
Yes it worked! thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.