more audit trails....

[Maclain]

Good morning all,

I've hijacked a simple audit trail from techrepublic and implemented in our main form.

the code is as follows:

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 " _
           & "tblAudit (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 Sub

in the before update of my form i have

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, Job_No)
End Sub

Now my questions are:
1. I have a number of combo box's that this module will not track. to track these would I change

If .ControlType = acTextBox Then

to

Select Case ctl.ControlType
  Case acTextBox, acComboBox then

2. Some of our fields contain " and \ and various other characters. for example in decription we may have 8" body. Problem is when a change is made to a field containing one of these characters, I get an error message "Syntax error (missing operator) in query "'8" body"

Obviously this is because it's picking up the 8" as part of an expression. How can I get it to just treat everything as plain old text?

 

[spikepl]

1. Case acTextBox, acComboBox then

would give you the bound value of the combobox, which seldom is comprehensible to humans. You'd have to fiddle around to get the actual displayed before/after value

2. You could do a replace on all input strings so that a single " gets replaced by ""
Or Const cDQ As String = "'" but if you also have ' as part of your texts, then this will fail too.
You could use queryDef and parameters, google those. That would in fact be the best solution.