如何强制重新查询填充ComboBox的Query以显示新添加记录？

我有一个绑定查询qryTipoDeVoz的ComboBox控件cboTipoVoz，该查询从数据表获取记录。向表中添加新记录NewVoice后，通过UpdateQuery子程序给查询追加筛选条件，接着执行Me.cboTipoVoz.Requery，但下拉列表里还是看不到这条新记录（已确认新记录在表中，且查询的SQL已经更新）。

主代码 #

' NewVoice has just been added to the table

' Updates the Query
Call UpdateQuery(NewVoice)

' Check whether the query has been updated with "NewVoice"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("qryTipoDeVoz")
With rst
    .MoveLast
    .MoveFirst
    Do While Not .EOF
        Debug.Print .Fields(0), .Fields(1)
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing

Me.cboTipoVoz.Undo

Me.cboTipoVoz.Requery

' Now, I can't see the "NewVoice" in the drop list. What is wrong here?

UpdateQuery子程序 #

Public Sub UpdateQuery(strVoice As String)

Dim qdf As QueryDef
Dim strSQL As String
Dim strLeft As String
Dim strRight As String
Dim ipos As Integer
Dim strAdd As String

Set qdf = CurrentDb.QueryDefs("qryTipoDeVoz")
strSQL = qdf.SQL

ipos = InStr(strSQL, ";")
strSQL = Left(strSQL, ipos)

ipos = InStr(strSQL, "ORDER") - 5
strLeft = Left(strSQL, ipos)
strRight = Right(strSQL, Len(strSQL) - ipos)

strAdd = " Or (tblTipoIntervencao.TipoDeIntervencao)='" & strVoice & "'"

qdf.SQL = strLeft & strAdd & strRight

End Sub

问题分析与解决方法 #

核心问题 #

1. SQL拼接逻辑存在硬编码隐患：
   原代码用InStr(strSQL, "ORDER") -5拆分SQL，完全依赖ORDER前固定的空格数，一旦原查询的SQL格式变化（比如空格数不同、注释存在），就会拆分错误，导致新条件拼错位置，直接破坏SQL结构。
2. 未处理特殊字符：如果NewVoice包含单引号，会直接导致SQL语法错误，查询无法正确返回数据。
3. 冗余操作干扰控件状态：Me.cboTipoVoz.Undo是撤销控件的用户输入，与重新加载数据无关，可能导致控件状态异常。

修复方案 #

方案1：修正UpdateQuery的SQL拼接逻辑

Public Sub UpdateQuery(strVoice As String)
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim orderPos As Integer
    Dim wherePos As Integer
    
    Set qdf = CurrentDb.QueryDefs("qryTipoDeVoz")
    strSQL = Replace(qdf.SQL, ";", "") ' 移除末尾分号
    
    ' 定位ORDER BY子句位置，分离排序部分
    orderPos = InStr(1, strSQL, "ORDER BY", vbTextCompare)
    Dim orderPart As String
    If orderPos > 0 Then
        orderPart = Mid(strSQL, orderPos)
        strSQL = Left(strSQL, orderPos - 1)
    Else
        orderPart = ""
    End If
    
    ' 转义单引号，避免SQL语法错误
    Dim escapedVoice As String
    escapedVoice = Replace(strVoice, "'", "''")
    Dim newCondition As String
    newCondition = "(tblTipoIntervencao.TipoDeIntervencao='" & escapedVoice & "')"
    
    ' 处理WHERE子句
    wherePos = InStr(1, strSQL, "WHERE", vbTextCompare)
    If wherePos > 0 Then
        ' 已有WHERE，追加OR条件（避免重复添加相同条件）
        If InStr(1, Mid(strSQL, wherePos), newCondition, vbTextCompare) = 0 Then
            strSQL = strSQL & " OR " & newCondition
        End If
    Else
        ' 无WHERE，添加新WHERE子句
        strSQL = strSQL & " WHERE " & newCondition
    End If
    
    ' 拼接回排序部分和分号
    qdf.SQL = strSQL & " " & orderPart & ";"
    Set qdf = Nothing
End Sub

方案2：直接动态设置ComboBox的RowSource（更安全）

没必要修改持久化的查询定义，直接给ComboBox设置动态SQL，避免污染原有查询：

' 替换主代码中的UpdateQuery调用及后续逻辑
Dim originalSQL As String
originalSQL = Replace(CurrentDb.QueryDefs("qryTipoDeVoz").SQL, ";", "")

' 转义特殊字符
Dim escapedVoice As String
escapedVoice = Replace(NewVoice, "'", "''")
Dim newCondition As String
newCondition = "(tblTipoIntervencao.TipoDeIntervencao='" & escapedVoice & "')"

' 拼接新SQL
Dim newSQL As String
If InStr(1, originalSQL, "WHERE", vbTextCompare) > 0 Then
    newSQL = originalSQL & " OR " & newCondition & ";"
Else
    newSQL = originalSQL & " WHERE " & newCondition & ";"
End If

' 直接绑定到ComboBox
Me.cboTipoVoz.RowSource = newSQL
Me.cboTipoVoz.Requery

额外检查点 #

• 执行修复后的代码后，手动打开qryTipoDeVoz查询，确认能查到NewVoice记录。
• 移除主代码中的Me.cboTipoVoz.Undo语句，避免干扰控件状态。

内容的提问来源于stack exchange，提问作者Antonio