VBA OpenRecordset产生错误3061 [英] VBA OpenRecordset Produces Error 3061

问题描述

databasename = "qryDataExport"
Dim grpfield As String
grpfield = "Group"

Dim keys As DAO.Recordset
groupcmd = "SELECT [" & databasename & "].[" & grpfield & "] FROM [" & databasename & "] GROUP BY [" & databasename & "].[" & grpfield & "]"
Set keys = CurrentDb.OpenRecordset(groupcmd, dbOpenSnapshot)

上面产生错误3061:参数太少.预期为13."运行时.到目前为止，我的阅读严重暗示这可能是拼写问题，其中字段标题不正确，或者是由定义groupcmd的行中引号不正确引起的.

The above produces "Error 3061: Too few parameters. Expected 13." when run. My reading thus far has heavily implied that this is likely a spelling issue with improper field titles or an issue caused by improper quotations in the line defining groupcmd.

我尝试使用以下格式的数据库名称:

I have attempted the following formats for databasename:

CurrentDb.Queries.qryDataExport
CurrentDb!Queries!qryDataExport

和上面的"qryDataExport".后两个不提供错误消息，而前一个不编译.我已经确认在主表和qryDataExport中都有一个名为Group的列.

And the above "qryDataExport". The latter two provide no error messages, while the first does not compile. I have confirmed that there is a column titled Group in both the main table and in qryDataExport.

正在使用的模块来自此Google代码页.

(截至目前的完整编辑模块: http://pastebin.com/TJip86ED )

( Full edited module as of this time: http://pastebin.com/TJip86ED)

从我所看到的情况来看，我希望这是数据库名称定义中一个非常明显的格式错误，但是我没有足够的VBA经验来发现它，并且我的想法已经用完了.任何建议将不胜感激.

From what I've seen, I expect this is an incredibly obvious formatting error in the databasename definition, but I haven't got enough experience with VBA to spot it and I'm running out of ideas. Any suggestions would be greatly appreciated.

generateKML()的内容现在在ExportToKMLButton_Click()中，其中ExportToKMLButton是表单DW_Form上的按钮.打开DW_Form时，查询qryDataExport可用，但是关闭窗体时，查询提示输入错误消息中提到的13个参数.

The content of generateKML() is now in ExportToKMLButton_Click(), where ExportToKMLButton is a Button on the Form DW_Form. While DW_Form is open, the query qryDataExport is usable, but when the form is closed, the query prompts for the 13 parameters mentioned in the error message.

推荐答案

听起来您的 qryDataExport 查询引用了Access窗体上的控件，也许与此类似...

It sounds like your qryDataExport query references controls on an Access form, perhaps similar to this one ...

SELECT *
FROM YourTable
WHERE some_field = Forms!Form1!YourTextBox

如果在窗体视图中打开了 Form1 ，我可以从Access的查询设计器中运行该查询，它将解析对窗体控件的引用.

If Form1 is open (in Form View), I can run that query from Access' query designer, and it will resolve the reference to the form control.

但是，如果我尝试对OpenRecordset使用完全相同的查询，则该引用将无法解析，在这种情况下，Access会将其解释为我未提供值的参数.

However, if I try to use the exact same query with OpenRecordset, the reference is not resolved and, in that context, Access interprets it to be a parameter for which I have not supplied a value.

对于具有多个控件引用的查询，可以基于SELECT语句创建一个临时QueryDef，并遍历其Parameters集合，为每个参数值提供参数的.Name中的Eval().最后调用QueryDef.OpenRecordset方法加载您的记录集:

For your query with multiple control references, you can create a temporary QueryDef based on your SELECT statement, and loop through its Parameters collection, supplying each parameter value with Eval() of the parameter's .Name And finally call the QueryDef.OpenRecordset method to load your recordset:

Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef(vbNullString, groupcmd)
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
Set keys = qdf.OpenRecordset

这篇关于VBA OpenRecordset产生错误3061的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！