Access多零件号搜索VBA代码无返回无报错求助

你的代码核心问题有两个：

• 拆分出的零件号数组完全未被使用，SQL仍在匹配包含整个逗号分隔字符串的记录，而非逐个匹配每个零件号
• 记录集绑定方式错误，关闭记录集后列表框会丢失数据源

具体修复步骤： #

1. 遍历拆分后的零件号数组，构建OR连接的匹配条件
2. 处理输入中的空格，避免因逗号后带空格导致匹配失败
3. 直接用SQL字符串作为列表框数据源，无需额外记录集操作，更稳定可靠
4. 增加空输入判断，防止生成无效SQL

修复后的完整代码： #

Option Compare Database

Private Sub Command7_Click()

End Sub

Public Sub btnSearch2_Click()
    Dim ArrayList() As String
    Dim strSQL As String
    Dim i As Integer
    Dim strConditions As String
    
    ' 处理空输入
    If Trim(Me.txtSearchCriteria2.Value) = "" Then
        Me.lstSearchResults2.RowSource = ""
        MsgBox "请输入搜索零件号"
        Exit Sub
    End If
    
    ' 拆分参数并去除每个元素的空格
    ArrayList = Split(Me.txtSearchCriteria2.Value, ",")
    For i = LBound(ArrayList) To UBound(ArrayList)
        ArrayList(i) = Trim(ArrayList(i))
        ' 构建模糊匹配的OR条件
        strConditions = strConditions & " OR Manufacturer_Part_Number ILike '*" & ArrayList(i) & "*'"
    Next i
    
    ' 去掉条件开头多余的OR
    strConditions = Mid(strConditions, 4)
    
    ' 构建完整SQL语句
    strSQL = "SELECT Manufacturer_Part_Number, CSP_Number, Rec_Min_Qty, CSP_Type " & _
             "FROM CSP_Data " & _
             "WHERE " & strConditions
    
    ' 绑定数据源到列表框
    Me.lstSearchResults2.RowSourceType = "Table/Query"
    Me.lstSearchResults2.RowSource = strSQL
    Me.lstSearchResults2.Requery
End Sub

额外优化选项： #

如果需要精确匹配零件号（而非模糊包含），可以改用IN语句提升效率：

' 替换条件构建部分
Dim strInList As String
For i = LBound(ArrayList) To UBound(ArrayList)
    strInList = strInList & ",'" & Trim(ArrayList(i)) & "'"
Next i
strInList = Mid(strInList, 2) ' 去掉开头的逗号

strSQL = "SELECT Manufacturer_Part_Number, CSP_Number, Rec_Min_Qty, CSP_Type " & _
         "FROM CSP_Data " & _
         "WHERE Manufacturer_Part_Number IN (" & strInList & ")"

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