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

Posted on 2011-04-26
Last Modified: 2012-06-21
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
    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
  Set ctl = Nothing
  Exit Sub

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

Open in new window

Question by:HudsonMarine
    LVL 142

    Accepted Solution

    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 & ", " _
    LVL 1

    Author Comment

    thanks angelll, is this audit trail considered a function?

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

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 1

    Author Closing Comment

    Yes it worked! thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now