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)
The above results in the error "Error 3061: Too few parameters". Expected 13. "at startup. My reading so far has pretty much implied that this is probably a spelling issue with incorrect field names or an issue caused by incorrect quotes in the line defining the cmd group.
I have tried using the following formats for databasename:
CurrentDb.Queries.qryDataExport
CurrentDb!Queries!qryDataExport
And higher "qryDataExport"
. The last two contain no error messages, and the first one does not compile. I have confirmed that there is a column called "Group" in both the main table and qryDataExport.
Module used: this google codes page .
(EDIT: Fully edited module at the moment: http://pastebin.com/TJip86ED )
From what I've seen, I expect this to be an incredibly obvious formatting error in defining the database name, but I don't have enough VBA experience to figure it out, and I'm running out of ideas. Any suggestions would be greatly appreciated.
EDIT2: The content is generateKML()
now in ExportToKMLButton_Click()
, where ExportToKMLButton
is the button on the form DW_Form
. While DW_Form
open, the request qryDataExport
can be used, but when the form is closed, the request asks for the 13 parameters mentioned in the error message.
source to share
It looks like your control of the qryDataExport queries on the Access form is perhaps similar to this ...
SELECT *
FROM YourTable
WHERE some_field = Forms!Form1!YourTextBox
If Form1 is open (in form view), I can run this query from Access Query Designer and it will resolve the link to the form control.
However, if I try to use the same query with OpenRecordset
, the link is not resolved, and in this context Access interprets it as a parameter for which I did not provide a value.
For your request with multiple control references, you can create a temporary QueryDef
based on your operator SELECT
and loop through its collection Parameters
by specifying each parameter value of the Eval()
parameter .Name
And finally, call the method QueryDef.OpenRecordset
to load the 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
source to share
The way you use the database name correctly ( databasename = "qryDataExport"
), qryDataExport is probably filtering the data using values from the form ... so when executing the query itself, the query detects that the 13 arguments it requires from the specified form are missing.
You can run this procedure on the Click () event for a button on a form, it should work.
source to share