CODEWITHSUNDEEP
ms-accessvba
Nathaniel Bendinsky
Nathaniel Bendinsky

Reputation: 143

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

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.

The module being used is from this Google Code page.

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

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.

EDIT2: 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.

Upvotes: 4

Views: 1800

Answers (2)

HansUp
HansUp

Reputation: 97101

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

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.

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.

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

Upvotes: 5

Manuel Castro
Manuel Castro

Reputation: 138

The way you use databasename is correct (databasename = "qryDataExport"), qryDataExport is likely filtering data using values from the form... that's why when you execute the query independently, the query finds it is missing 13 paramenters that it takes from said form.

You can run this procedure in a Click() event for a button within the form, it should work.

Upvotes: 0

Related Questions