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?

Answer 1

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.