参数太少预期1，记录集问题 [英] Too few parameters Expected 1, recordset issue

问题描述

我在从MS Access 2010数据库中创建的查询中获取记录集以运行时遇到问题.这是我要运行的代码:

I'm having a problem getting a recordset to run from a query I created in an MS Access 2010 database. here is t he code I want to run:

Private Sub Command192_Click()
Dim recs As String
Dim param As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("UnitRec_Qry", dbOpenDynaset)

With rs
.MoveLast
.MoveFirst

While Not .EOF

recs = recs & vbNewLine & !Spara & " - " & !Rec
.MoveNext

Wend

End With


MsgBox (recs)
End Sub

这应该输出的是一个消息框，其中包含列表中查询的许多记录.我这样做是为了将它和许多其他记录收集到一个文本文件中，以进行复制和粘贴到一个单独的系统中.目前，我正在运行这段代码，以便将其全部放入一个字符串变量中.

What this should output is a message box with a number of records from the query in a list. I do this so I can gather this and a number of other records into a text file for copying and pasting into a separate system. At the moment, I'm running this code so I can place it all into a string variable.

我的问题是我遇到了可怕的预期参数太少1"错误.

My problem is that I'm getting the dreaded "Too Few parameters expected 1" error.

查询有效，我将其保存到数据库中并进行了测试，并获得了预期的结果.

The query works, I've saved it into the database and tested it and I get the expected results.

我尝试使用SQL运行记录集:

I tried running the recordset with SQL:

Set rs = CurrentDb.OpenRecordset("SELECT UnitRecommend_tbl.URecID, UnitRecommend_tbl.Spara," _
& " UnitRecommend_tbl.Rec, UnitRecommend_tbl.SvyID" _
& " FROM UnitRecommend_tbl" _
& " WHERE ((UnitRecommend_tbl.SvyID) = [Forms]![SurveyRegister_frm]![SurveyID])" _
& " ORDER BY UnitRecommend_tbl.Spara;", dbOpenDynaset)

我遇到同样的错误

我再次运行它，但是删除了"WHERE"语句，代码运行得很好，但是给了我表中的每条记录.不是我想要的.

I ran it again but removed the "WHERE" statement and the code ran just fine, but gave me every record in the table. Not what I wanted.

因此，这些字段可以正常运行，因为数据可以运行.当我调试文本时，SQL中的参数确实显示为正确的参数，在这种情况下，数字4是整数.

So, the fields are OK because the data runs. When I debug the text the parameter in the SQL does show up as the right parameter, in this case, the number 4 which is an integer.

所以我在这里不知所措，我在这里搜索了其他帖子，并尝试了这些可能的解决方案(除非我错过了什么).

So I'm at a loss here, I've searched through the other posts here and I have tried these possible solutions (unless I missed something).

我也尝试使用dbopensnapshot，但仍然没有乐趣.想知道我现在是否在使用正确的代码.

I also tried using dbopensnapshot as well, still no joy. Wondering if I'm using the right code here now.

任何帮助都会很棒.

欢呼

推荐答案

如果在VBA中打开记录集，则不会自动评估类似[Forms]![SurveyRegister_frm]![SurveyID]的参数.

A parameter like [Forms]![SurveyRegister_frm]![SurveyID] doesn't get evaluated automatically if you open a recordset in VBA.

使用此功能:

Public Sub Eval_Params(QD As DAO.QueryDef)

On Error GoTo Eval_Params_Err

    Dim par As DAO.Parameter

    For Each par In QD.Parameters
        ' This is the key line: Eval "evaluates" the form field and gets the value
        par.Value = Eval(par.Name)
    Next par

Eval_Params_Exit:
    On Error Resume Next
    Exit Sub

Eval_Params_Err:
    MsgBox Err.Description, vbExclamation, "Runtime-Error " & Err.Number & " in Eval_Params"
    Resume Eval_Params_Exit

End Sub

带有这样的QueryDef对象:

with a QueryDef object like this:

Dim QD As QueryDef
Dim RS As Recordset

Set QD = DB.QueryDefs("UnitRec_Qry")
Call EVal_Params(QD)
Set RS = QD.OpenRecordset(dbOpenDynaset)

或者，您可以通过将参数移到SQL字符串之外，在VBA代码中与SQL一起运行它:

Alternatively, you can run it with SQL in the VBA code by moving the parameter outside of the SQL string:

Set rs = CurrentDb.OpenRecordset("SELECT UnitRecommend_tbl.URecID, UnitRecommend_tbl.Spara," _
& " UnitRecommend_tbl.Rec, UnitRecommend_tbl.SvyID" _
& " FROM UnitRecommend_tbl" _
& " WHERE ((UnitRecommend_tbl.SvyID) = " & [Forms]![SurveyRegister_frm]![SurveyID] & ")" & _
& " ORDER BY UnitRecommend_tbl.Spara;", dbOpenDynaset)

这篇关于参数太少预期1，记录集问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！