Access: "Too few parameters."

Question

I know this is a redundant question but could not find a similar scenario to mine.

I have a stored query which, when executed, picks values from the filters on an opened form and display results. All works.

Here is how the query is called:

cmd.CommandType = adCmdStoredProc cmd.CommandText = "qry" Dim rsReport As New ADODB.Recordset rsReport.CursorType = adOpenKeyset rsReport.CursorLocation = adUseClient rsReport.Open cmd

I am trying to use the same query from VBA to create Excel files which can be downloaded or emailed and I am getting a "Too few parameters" error now (while the form is still open). Can anyone set me in the right direction why this is happening please?

I can understand you have a problem, but we cannot help without any code. — Prisoner
– Prisoner, Commented Aug 9, 2017 at 9:45

Erik A · Accepted Answer · 2017-08-09 10:08:33Z

When executing a query using VBA, you can't reference open forms. You need to explicitly state all parameters.

If you're executing the query using DoCmd.RunQuery, you can set parameters using DoCmd.SetParameter.

If you're executing the query using QueryDef.Execute, you can set parameters using the QueryDef.Parameters collection.

If you're executing the query using an ADODB.Command, you can set parameters by appending parameters to the Command.Parameters collection, in the following way:

Command.Parameters.Append Command.CreateParameter ("MyParameterName", adInteger, adParamInput) where adInteger is the type. After that, you still need to set the parameter to a value by setting Command.Parameters("MyParameterName").Value = MyValue

Also see this question for info on ADODB parameters. They are a bit more tricky.

All parameters need to be filled in before executing the query.

the cmd in question is ADODB.Command object. which method is used?
One more question, I am referencing the fields directly in query. So I create the parameters with the same naming convention? That is "Forms!form1!textfield"
Usually, you can just add parameters in order and it will work, but for ADODB, I'm not entirely sure. I usually just go for ? placeholders for parameters in the query.
I am accepting your answer since it did help me with the direction, and now it's a matter of passing the arguments. Thanks, have a good day

Collectives™ on Stack Overflow

Access: "Too few parameters."

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related