Access窗体按钮无法执行OpenRecordSet VBA代码问题排查

你的代码确实通过OpenRecordset成功加载了查询对应的记录集，但只是把数据读到了内存中，没有执行任何展示或输出操作，所以点击按钮后看不到任何反馈。另外代码缺少错误捕获逻辑，即使执行中出现隐性错误也不会弹出提示。

下面是具体的修改方案：

1. 先添加错误捕获，排查潜在问题 #

给代码加上错误处理，确保能看到执行时的错误信息，避免隐性问题：

Private Sub ManuReport_Click()
    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim strSQL As String '统一变量名大小写，避免混淆

    On Error GoTo ErrorHandler '开启错误捕获

    Set dbs = CurrentDb

    strSQL = "SELECT " & _
             "dbo_VENDOR1.ITEM_NO," & _
             "dbo_VENDOR1.ITEM_PRICE," & _
             "dbo_VENDOR2.ITEM_NO," & _
             "dbo_VENDOR2.ITEM_PRICE," & _
             "dbo_VENDOR1.MANUFACTURER_ITEM_NO," & _
             "dbo_VENDOR1.MANUFACTURER," & _
             "dbo_VENDOR1.ITEM_NAME " & _
             "From dbo_VENDOR2 " & _
             "INNER JOIN dbo_VENDOR1 " & _
             "ON dbo_VENDOR2.MANUFACTURER_ITEM_NO = dbo_VENDOR1.MANUFACTURER_ITEM_NO " & _
             "WHERE dbo_VENDOR1.MANUFACTURER IN ('MANUFACTURER CODE') " & _
             "And dbo_VENDOR1.ITEM_PRICE > dbo_VENDOR2.ITEM_PRICE "

    Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    '检查是否查询到数据
    If Not rsSQL.EOF And Not rsSQL.BOF Then
        MsgBox "查询到" & rsSQL.RecordCount & "条数据"
    Else
        MsgBox "未查询到任何数据"
    End If

ExitSub:
    '清理对象，避免内存泄漏
    If Not rsSQL Is Nothing Then rsSQL.Close
    Set rsSQL = Nothing
    Set dbs = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "执行出错：" & Err.Description & vbCrLf & "错误代码：" & Err.Number
    Resume ExitSub
End Sub

2. 将数据展示到窗体（常用场景） #

如果要直接在窗体上显示查询结果，可以绑定到子窗体：

• 先在主窗体添加一个子窗体控件（命名为subReportData）
• 在打开记录集后添加以下代码：

Set Me.subReportData.Form.Recordset = rsSQL

3. 导出到Excel（如需导出数据） #

如果要把查询结果导出到Excel，替换MsgBox部分的代码为：

Dim xlApp As Object
Dim xlWB As Object

If Not rsSQL.EOF And Not rsSQL.BOF Then
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWB = xlApp.Workbooks.Add
    '复制记录集数据到Excel
    rsSQL.MoveFirst
    xlWB.Sheets(1).Range("A1").CopyFromRecordset rsSQL
    '添加表头
    Dim i As Integer
    For i = 0 To rsSQL.Fields.Count - 1
        xlWB.Sheets(1).Cells(1, i + 1).Value = rsSQL.Fields(i).Name
    Next i
End If

注意事项 #

• 变量名StrSQL和strSQL大小写不一致，虽然VBA不区分大小写，但统一命名可避免混淆
• 后续引用窗体文本框参数时，直接在SQL字符串中拼接即可，比如把'MANUFACTURER CODE'替换为'" & Me.txtManufacturerCode & "'"（注意引号嵌套规则）

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