Too Few Parameters error in parameter query

[Susie]

I've got an append parameter query where I have [Enter Form Type] in the criteria of a field (this is the only parameter). I've also put this in Queries/Parameters:
Parameter Data Type
[Enter Form Type] text

I'm trying to set the parameter in VBA and run the query, but I keep getting the error "too few parameters, expected 1"

Here's my code:
Dim MyDB As Database, MyQuery As QueryDef
Set MyDB = CurrentDb
Set MyQuery = MyDB.QueryDefs("q_GetRecordsForOneFormType&quot
MyQuery("[Enter Form Type]&quot = "A"
MyDB.Execute (MyQuery.Name)

I've also tried the following
MyQuery("Enter Form Type&quot = "A"
MyQuery.Parameters("Enter Form Type&quot = "A"
MyQuery.Parameters("[Enter Form Type]&quot = "A"

but nothing seems to work. Any ideas?
TIA

 

[MichaelRed]

one "example":

Set qdfInv = dbs.QueryDefs("qryMyInvoice&quot
qdfInv.Parameters("MyInv&quot = rstInv!InvNum
Set rstMyInv = qdfInv.OpenRecordset()



This approach is very close to what you already have. It "adds" a recordset variable ("MyInv&quot which is the receipient of the querydef action.

You "should" be able to adapt your code to do this?



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Susie]

I didn't totally follow the above. You're creating an on-the-fly parameter called MyInv, correct? What is rstInv? Where does that get defined? Or is that the table in qryMyInvoice?

 

[MichaelRed]

. You're creating an on-the-fly parameter called MyInv, correct?

A}[tab]No. The "MyInv" is the name of the parameter. Equivalent to your "Enter Form Type".

What is rstInv?

A)[tab]rstInv is just a recordset.

Where does that get defined?
A)[tab]It is Defined in the module/procedure w/ a dim statement. e.g Dim rstInv as Recordset. The only other 'reference' is in the fragment posted above, and where ever the results are used (however in this situation it MUST be within the procedure - as it ceases to exist (goes out of scope) when the procedure is exited (goes out of scope)).

In the procedure where I use this approach, I continue with a loop through the recordset doing some calculations where I need to save specific field values from previous records and apply them in a calculation with field values from the current record. Very inefficient, but then the record set is not well normalized for the info I need to extract.

Since your post didn't include any indication of what/why/who/when/where/how the parameter query result set would be applied, I did not attempt to infer any particular application.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Susie]

Ah, I see. My query is a make-table query, so I'm trying to end up with a table which contains only the records that match.

So, it looks like the difference between your code and mine is that I'm trying to execute the query after setting the parameter, thereby creating a new table, and you are accessing the recordset after setting the parameter.

So, if I tried to use your method, I would have to build the table myself in vba rather than relying on the make-table query. Which I could do, I guess, but I was hoping there is an easy answer as to why I get the error when I try to run the make-table. I've found in the past that scrolling through recordsets and building tables from them seems to be slower than running a make-table query.

 

[Susie]

I got it to work!!!! Yippee!!!!

The answer turned out to be very simple.

Instead of
MyDB.Execute (MyQuery.Name)

I needed to have:
MyQuery.Execute