Tracking the changed data in MS Access forms when controls of the form (ie. Text box) is blank

Question

I am using MS Access database and for tracking the users activities, I have used the below VBA Module: In the below code every changes in the Text boxes of my Access form is inserted into a log table by the name "Audit".

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 " _
           & "Audit (EditDate, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & 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

I can use the above code in before update event of my form. It works perfectly when my textboxes already have a value, but when these textboxes are blanks the subroutine does not work. Any suggestion will be appreciated.

Answer 1

While the approach shared by wazz works as long as there are no quotes in the text field, that's not the way to do it.

The proper way is to use parameters. That way you prevent SQL injection and errors that occur when your text fields contain quotes. This will also avoid the DoCmd.SetWarnings code.

strSQL = "INSERT INTO " _
           & "Audit (EditDate, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now(), ?, ?, ?, ?, ?)"
With CurrentDb.CreateQueryDef("", strSQL)
    .Parameters(0) = recordid.Value
    .Parameters(1) = frm.RecordSource
    .Parameters(2) = strControlName 
    .Parameters(3) = varBefore 
    .Parameters(4) = varAfter
    .Execute
End With

Fully implemented:

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
    'Avoid labels and other controls with Value property.
    If ctl.ControlType = acTextBox Then
      If ctl.Value <> ctl.OldValue Then
        varBefore = ctl.OldValue
        varAfter = ctl.Value
        strControlName = ctl.Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now(), ?, ?, ?, ?, ?)"
        With CurrentDb.CreateQueryDef("", strSQL)
            .Parameters(0) = recordid.Value
            .Parameters(1) = frm.RecordSource
            .Parameters(2) = strControlName 
            .Parameters(3) = varBefore 
            .Parameters(4) = varAfter
            .Execute
        End With
      End If
      End If
  Next
  Set ctl = Nothing
  Exit Sub

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