Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-04-26
Medium Priority
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
  • 2
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35467384
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 & ", " _

Author Comment

ID: 35467442
thanks angelll, is this audit trail considered a function?

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

LVL 143

Expert Comment

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

Author Closing Comment

ID: 35467531
Yes it worked! thanks!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

810 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