参数太少 预期 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.

使用这个功能:

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屋！