ms-access - db.OpenRecordset 返回的不是记录集；但按照他们所做的查询运行

Question

我试图按路线生成账单，因此我按属于特定路线的客户对其进行了分割，然后对每个客户汇总他们的每周费率以编制月费率。

问题是，即使使用 SELECT * IN [table] 打开记录集也不会返回任何结果，所以一定有一些明显的错误。这是我的代码，如果有人能帮我弄清楚，我将不胜感激。

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim custNo As Integer
Dim month_total_v As Integer
Dim weekTotal As Integer
Dim weekStart As Date
Dim sql As String

'sql = "SELECT cust_no FROM Roster WHERE route = Forms![routeBill]![route]"

Set rs = CurrentDb.OpenRecordset("SELECT CUST_NO FROM Roster WHERE ROUTE = 'Forms![routeBill]![route]'")

month_total_v = 0

MsgBox ("Boop.")

If Not (rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    MsgBox ("Boop.")
        custNo = rs!CUST_NO
        Set rs2 = CurrentDb.OpenRecordset("SELECT wk_rate, wk_strt_dt FROM Roster WHERE wk_strt_dt >= Forms![routeBill]![Text53] AND wk_strt_dt <= Forms![routeBill]![Text4] AND cust_no = custNo")
        If Not (rs2.EOF And rs2.BOF) Then
            rs2.MoveFirst
            Do Until rs2.EOF = True
                MsgBox "Boop."
                weekStart = WK_STRT_DT
                month_total_v = month_total_v + rs2!WK_RATE
                Set rs3 = CurrentDb.OpenRecordset("SELECT * FROM monthTotal where cust_no = custNo and billMonth=month(weekStart) and billYear=year(weekStart)") 'specify date ranges to pick from to shorten query
                If rs3.EOF Then
                    sql = "INSERT INTO monthTotal (cust_no, month_total, billMonth, billYear) VALUES (custNo, month_total_v, month(weekStart), year(weekStart))" 'Append, record does not exist
                    DoCmd.RunSQL sql
                Else
                    sql = "UPDATE monthTotal SET month_total = month_total_v WHERE cust_no = custNo AND billMonth = month(weekStart) AND billYear = year(weekStart)" 'Update, record exists
                    DoCmd.RunSQL sql
                End If
                rs2.MoveNext
            Loop
        Else
            'pass
        End If
    rs.MoveNext
    Loop
End If

Answer 1

当所有存储的 ROUTE 值都不包含文字文本时，此查询将不会返回任何记录，'Forms![routeBill]![route]' ...

SELECT CUST_NO FROM Roster WHERE ROUTE = 'Forms![routeBill]![route]'

在其他地方，您有一个 WHERE 子句，其中包括 AND cust_no = custNo。但是，由于 custNo 是一个 VBA 变量，数据库引擎对其一无所知，并将其解释为您尚未为其提供值的参数名称。

您可以通过在 DAO.QueryDef 中使用参数查询来避免这些类型的问题。然后提供参数值(来自表单控件、VBA 变量等...)并使用 QueryDef.OpenRecordset 方法加载您的记录集。

这是一个简单的例子......

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSelect As String

strSelect = "SELECT CUST_NO FROM Roster WHERE ROUTE = [which_route]"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("which_route").Value = Forms![routeBill]![route]
Set rs = qdf.OpenRecordset
With rs
    If .BOF And .EOF Then
        MsgBox "no matches found"
    Else
        .MoveLast
        MsgBox .RecordCount & " matches"
    End If
    .Close
End With

请注意，参数查询技术无需在文本值周围添加引号(然后还可以处理其中可能包含引号的文本值)和格式化日期/时间值并将它们括在 # 中分隔符。