0

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?

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Babar
  • 1,202
  • 1
  • 12
  • 21

1 Answers1

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • the cmd in question is ADODB.Command object. which method is used? – Babar Aug 09 '17 at 09:55
  • 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" – Babar Aug 09 '17 at 10:01
  • 1
    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. – Erik A Aug 09 '17 at 10:03
  • 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 – Babar Aug 09 '17 at 10:05