当控制表单(即.文本框)为空

Question

我正在使用MS Access数据库并跟踪用户活动，我使用了以下VBA模块：在下面的代码中，我的 Access 窗体的文本框中的每个更改都将按名称"审核"插入到日志表中。

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

我可以在表单的更新事件之前使用上面的代码。当我的文本框已经有一个值时，它可以完美地工作，但是当这些文本框为空白时，子例程不起作用。任何建议将不胜感激。

Answer 1

虽然 wazz 共享的方法只要文本字段中没有引号就可以工作，

但这不是这样做的方法。

正确的方法是使用参数。这样，可以防止 SQL 注入和文本字段包含引号时发生的错误。这也将避免DoCmd.SetWarnings代码。

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

完全实施：

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