ms-access - 使用嵌套查询打开 Recordset

Question

我正在尝试在 Access VBA 中打开一个记录集。我在 VBA 中编写了我的 SQL 字符串，因为我正在使用带有变量的 Select Top。目前我收到运行时错误 3061 - 参数太少。预计 1

我的 SQL 字符串正在将表单填充为 RecordSource，因此我知道该字符串是有效的。当我尝试打开该记录集以进一步操作数据时，我的问题就出现了。

我相信这个错误是由于我的 SQL 字符串引用了数据库中的另外两个查询。有没有办法绕过参数错误？

这是我的代码。如果格式不正确，请见谅

Private Sub Form_Load()

Dim skip As String
Dim sqlstr As String

skip = "select [skip lot qty] from [Requirements] where [requirements].[part no] = eval('[forms]![main menu]![part no]')"


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(skip)


Dim SLQ As Integer

SLQ = rs![skip lot qty]

Dim vendor As String
vendor = [Forms]![Main Menu]![Vendor No]

sqlstr = "SELECT TOP " & SLQ & " [Vendor Log].[ID],[Vendors Query].[Vendor Name], [Vendor Log].[Vendor No], [Vendor Log].[Date], [Vendors Query].[Last Qtr Rating], "

sqlstr = sqlstr & "[Vendor Log].[Part No] , [Requirements Query].[Skip Lot], [Requirements Query].[Plan No], [Vendor Log].[Inspection Req'd], [Vendor Log].[PO Number], "

sqlstr = sqlstr & "[Vendor Log].[Qty Received], [Vendor Log].[Sample Qty], [Vendor Log].[Accept Lot], [Vendor Log].[Comments], [Vendor Log].[Inspector Clock No], "

sqlstr = sqlstr & "[Vendor Log].[Counter], [Requirements Query].[Rev Date], [Requirements Query].[Rev Note], IIf([Accept Lot]=""R"",1,0) AS [R Counter], "

sqlstr = sqlstr & "IIf([Inspection Req'd]=""Yes"",1,0) AS [Yes Counter], IIf([Dim A]=""N/A"",""No"",""Yes"") AS [Dim Insp], IIf([Accept Lot] Is Null,1,0) AS"

sqlstr = sqlstr & " [Null Counter], [Requirements Query].[skip lot qty] FROM [Vendors Query] INNER JOIN ([Requirements Query] INNER JOIN [Vendor Log] ON "

sqlstr = sqlstr & " [Requirements Query].[Part No] = [Vendor Log].[Part No]) ON [Vendors Query].[Vendor No] = [Vendor Log].[Vendor No]"

sqlstr = sqlstr & " WHERE ((([Vendor Log].Date) > (Date - 1095)) And (([Vendor Log].[Vendor No]) = '" & vendor & "')) ORDER BY [Vendor Log].[ID] DESC;"

Debug.Print sqlstr
''setting the record source of the form to the string
Me.RecordSource = sqlstr

Dim rs2 As DAO.Recordset
Dim db As DAO.Database


Set db = CurrentDb


Set rs2 = db.OpenRecordset(sqlstr) >>this is the error

供应商日志是一个表，而需求查询和供应商查询是选择查询，它们使用先前表单中的信息引用表。

我想打开这个记录集，以便对字符串中的不同计数器求和。

我已将 sqlstr 粘贴到一个新查询中，它运行良好。我已经仔细检查了所有表名，并通过数据库运行了需求查询和供应商查询，没有任何问题。

请帮忙！

编辑：Debug.Print 的输出：

SELECT TOP 4 [Vendor Log].[ID],[Vendors Query].[Vendor Name], [Vendor Log].[Vendor No], [Vendor Log].[Date], [Vendors Query].[Last Qtr Rating], [Vendor Log].[Part No] , [Requirements Query].[Skip Lot], [Requirements Query].[Plan No], [Vendor Log].[Inspection Req'd], [Vendor Log].[PO Number], [Vendor Log].[Qty Received], [Vendor Log].[Sample Qty], [Vendor Log].[Accept Lot], [Vendor Log].[Comments], [Vendor Log].[Inspector Clock No], [Vendor Log].[Counter], [Requirements Query].[Rev Date], [Requirements Query].[Rev Note], IIf([Accept Lot]="R",1,0) AS [R Counter], IIf([Inspection Req'd]="Yes",1,0) AS [Yes Counter], IIf([Dim A]="N/A","No","Yes") AS [Dim Insp], IIf([Accept Lot] Is Null,1,0) AS [Null Counter], [Requirements Query].[skip lot qty] FROM [Vendors Query] INNER JOIN ([Requirements Query] INNER JOIN [Vendor Log] ON  [Requirements Query].[Part No] = [Vendor Log].[Part No]) ON [Vendors Query].[Vendor No] = [Vendor Log].[Vendor No] WHERE ((([Vendor Log].Date) > (Date - 1095)) And (([Vendor Log].[Vendor No]) = 'PO1296')) ORDER BY [Vendor Log].[ID] DESC;

编辑 2：对不起，这些是长查询！这是需求查询。[零件编号]为表格，[要求]为表格

SELECT DISTINCTROW TOP 1 [Part Numbers].[Part No], [Part Numbers].Description, [Part Numbers].[Matl Type], [Part Numbers].Illustration, [Part Numbers].Obsolete, [Part Numbers].UOM, Requirements.[Skip Lot], Requirements.[Plan No], Requirements.Material, Requirements.[ID Reference], Requirements.Comment, Requirements.Functional, Requirements.Finish, Requirements.Cosmetic, Requirements.[Packaging - General], Requirements.[Individual Label/Tag], Requirements.[UPC Code], Requirements.[Qty per Pack], Requirements.[Pack Label], Requirements.[I2of5 Pack], Requirements.[Qty per Case], Requirements.[Case Label], Requirements.[I2of5 Case], Requirements.[Case Check Req'd?], Requirements.[Case Length (In)], [Case Length (In)]-([Case Length (In)]*0.15) AS [Case L Min], [Case Length (In)]+([Case Length (In)]*0.15) AS [Case L Max], Requirements.[Case Width (In)], [Case Width (In)]-([Case Width (In)]*0.15) AS [Case W Min], [Case Width (In)]+([Case Width (In)]*0.15) AS [Case W Max], Requirements.[Case Height (In)], [Case Height (In)]-([Case Height (In)]*0.15) AS [Case H Min], [Case Height (In)]+([Case Height (In)]*0.15) AS [Case H Max], Requirements.[Case Weight], [Case Weight]-([Case Weight]*0.15) AS [Case Wt Min], [Case Weight]+([Case Weight]*0.15) AS [Case Wt Max], Requirements.Certs, Requirements.[Dim A], Requirements.[A Tolerance], Requirements.[Dim B], Requirements.[B Tolerance], Requirements.[Dim C], Requirements.[C Tolerance], Requirements.[Dim D], Requirements.[D Tolerance], Requirements.[Dim E], Requirements.[E Tolerance], Requirements.[Dim F], Requirements.[F Tolerance], Requirements.[Dim G], Requirements.[G Tolerance], Requirements.[Dim H], Requirements.[H Tolerance], Requirements.[Other Dim], Requirements.[Other Tol], Requirements.[WLL (lbs)], Requirements.[WLL (kg)], Requirements.[BF Test Data], Requirements.[Breaking Force (lbs)], Requirements.[BF Test Req'd?], Requirements.[BF Sample], Requirements.Elongation, Requirements.Comments, Requirements.[Rev Date], Requirements.[Rev Note], IIf([Dim A]="N/A","No","Yes") AS [Dim Insp], Requirements.[Spec Ref], Requirements.[skip lot qty]
FROM [Part Numbers] INNER JOIN Requirements ON [Part Numbers].[Part No] = Requirements.[Part No]
WHERE ((([Part Numbers].[Part No])=[Forms]![Main Menu]![Part No]))
ORDER BY Requirements.[Rev Date] DESC;

Vendors 查询：Vendors 和 Ratings 都是表

SELECT Vendors.[Vendor No], Vendors.[Vendor Name], Vendors.Obsolete, Vendors.[Last Qtr Rating], Ratings.[Last Rating Score]
FROM Ratings INNER JOIN Vendors ON Ratings.[Last Qtr Rating] = Vendors.[Last Qtr Rating]
WHERE (((Vendors.Obsolete)=No))
ORDER BY Vendors.[Vendor Name];

标签： ms-accessvba

Answer 1

您遇到的问题是由于Database.OpenRecordset不能与包含参数的命名查询一起使用。您的需求查询包含对表单字段的引用，该字段是一个参数，必须使用 QueryDef 对象显式设置。

这个奇怪的怪癖也是为什么查询在所有其他情况下都可以接受的原因，当您通过Database.OpenRecordset.

最简单的解决方案是将 sqlstr 中的 SQL 保存为命名查询，通过 QueryDef 对象打开它，设置其参数（供应商和 [Forms]![Main Menu]![Part No]），然后通过调用打开记录集QueryDef.OpenRecordset.

代码看起来类似于：

    Dim qdf as QueryDef
    Set qdf = CurrentDb.QueryDefs("namedQuery") ' replace with name of new query

' optionally, use this RegExp to replace "SELECT TOP n" dynamically
' requires reference: Microsoft VBScript Regular Expressions 5.5
'    Dim regex As New RegExp
'    regex.IgnoreCase = True
'    regex.Global = True
'    regex.Pattern = "(SELECT TOP )[0-9]+"
'    qdf.SQL = regex.Replace(qdf.SQL, "SELECT TOP " & rs![skip lot qty])

    qdf.Parameters("vendor") = [Forms]![Main Menu]![Vendor No]
    qdf.Parameters("[Forms]![Main Menu]![Part No]") = [Forms]![Main Menu]![Part No]

    Set rs2 = qdf.OpenRecordset
    qdf.Close

新的命名查询看起来像这样（注意供应商参数）：

SELECT TOP 4 [Vendor Log].[ID],[Vendors Query].[Vendor Name], [Vendor Log].[Vendor No], [Vendor Log].[Date], [Vendors Query].[Last Qtr Rating], [Vendor Log].[Part No] , [Requirements Query].[Skip Lot], [Requirements Query].[Plan No], [Vendor Log].[Inspection Req'd], [Vendor Log].[PO Number], [Vendor Log].[Qty Received], [Vendor Log].[Sample Qty], [Vendor Log].[Accept Lot], [Vendor Log].[Comments], [Vendor Log].[Inspector Clock No], [Vendor Log].[Counter], [Requirements Query].[Rev Date], [Requirements Query].[Rev Note], IIf([Accept Lot]="R",1,0) AS [R Counter], IIf([Inspection Req'd]="Yes",1,0) AS [Yes Counter], IIf([Dim A]="N/A","No","Yes") AS [Dim Insp], IIf([Accept Lot] Is Null,1,0) AS [Null Counter], [Requirements Query].[skip lot qty] FROM [Vendors Query] INNER JOIN ([Requirements Query] INNER JOIN [Vendor Log] ON  [Requirements Query].[Part No] = [Vendor Log].[Part No]) ON [Vendors Query].[Vendor No] = [Vendor Log].[Vendor No] WHERE ((([Vendor Log].Date) > (Date - 1095)) And (([Vendor Log].[Vendor No]) = vendor)) ORDER BY [Vendor Log].[ID] DESC;

编辑：正如 HansUp 所指出的，TOP 只接受文字，不接受参数。如果您的表不是很大（10,000 多条记录），您可以在查询中静态指定 TOP n 或完全删除 TOP。或者您可以使用上面代码中包含的正则表达式

HansUp 的另一个优点是不再需要 RegExp。您也可以使用以下代码：

    Dim qdf as QueryDef
    Set qdf = CurrentDb.CreateQueryDef("") ' creates temporary querydef

    qdf.SQL = "SELECT TOP " & SLQ & "rest of sqlstr..."    

    qdf.Parameters("vendor") = [Forms]![Main Menu]![Vendor No]
    qdf.Parameters("[Forms]![Main Menu]![Part No]") = [Forms]![Main Menu]![Part No]

    Set rs2 = qdf.OpenRecordset
    qdf.Close