“参数太少错误".尝试在Access中使用VBA打开记录集时 [英] "Too few parameters error" when trying to open a recordset in Access with VBA

问题描述

我在上找到了另一个答案解决了这个问题，但对我没有帮助.我检查了我要引用的查询，但没有发现任何字段有问题.我还尝试了如何声明和设置对象及内容的方法，但是那也不起作用.

I found another answer on here that addressed this but it didn't help me. I checked the query I am trying to reference and I don't see a problem with any field. I also played around with how I declared and set the objects and stuff but that didn't work either.

Dim dbsCurrent As DAO.Database
Dim rst As DAO.Recordset

Set dbsCurrent = CurrentDb
Set qdf = CurrentDb.QueryDefs("qry_FilmZip")
Set rst = qdf.OpenRecordset 'The error points to this line

rad_full = rst!radius_full

MsgBox ("rad_full:" + rad_full)

---

更新:我尝试为.OpenRecordSet方法赋予查询名称，如下所示:Set rst = qdf.OpenRecordset("qry_FilmZip")

---

Update: I tried giving the .OpenRecordSet method the name of the query like this: Set rst = qdf.OpenRecordset("qry_FilmZip")

...但是现在它给了我一个新的错误:Run-time error 3421: Data type conversion error.有人知道发生了什么吗?错误指向同一行.

...but now it gives me a new error: Run-time error 3421: Data type conversion error. Anyone know what is going on? The error points to the same line.

我发现了如何解决第二个错误.原来我必须做

I found out how to resolve the 2nd error. It turns out that I had to do

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm

...但是我不知道这实际上是在做什么.有人可以启发我吗?

...but I don't understand what this is really doing. Could someone enlighten me?

SQL:

SELECT 

  tbl_FilmZipInfo.ID, 
  tbl_FilmZipInfo.item, 
  tbl_FilmZipInfo.qty_per_unit, 
  tbl_FilmZipInfo.unit_of_measure, 
  tbl_FilmZipInfo.radius_core, 
  tbl_FilmZipInfo.radius_full, 
  tbl_FilmZipInfo.Lf_value_for_zipper, 
  tbl_FilmZipInfo.S_value_for_zipper, 
  tbl_FilmZipInfo.film_or_zip, 
  tbl_FilmZipInfo.Comments, 
  tbl_FilmZipInfo.physical_description

FROM 

  tbl_FilmZipInfo

WHERE 

  (((tbl_FilmZipInfo.item)=[Forms]![frm_FilmZip]![Text314]));

推荐答案

来源:这里唯一需要的参数是您要打开的记录集的名称.

The only required argument here is the name of the recordset that you want to open.

expression .OpenRecordset(名称，类型，选项，LockEdit)

expression .OpenRecordset(Name, Type, Options, LockEdit)

在像您这样的参数查询中，您需要使用以下语法:

In a parameter query like yours, you need to use the following syntax:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef 'You don't dim your qdf
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qfd = dbs.QueryDefs("qryMyParameterQuery")

'Supply the parameter value
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7

'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset() 'Note the brackets here

您也可以在这里尝试以下类型:

You could also try the following type here:

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

'Open a table-type Recordset
Set rsTable = dbs.OpenRecordset("Table1", dbOpenTable)

'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("qryMyQuery", dbOpenDynaset)

这篇关于“参数太少错误".尝试在Access中使用VBA打开记录集时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！