Trouble With Parameters

[Guest]

I have a parameter union query which gets it's criteria from an open form.
I then have a Crosstab query which uses the union query to return results.

This works fine if I am in the database window. However, if I try to open
the Crosstab query as a recordset, I get an error stating "too few
parameters, expected 1."

Can someone help out on this?

Any help is greatly appreciated.

 

[Michel Walsh]

Sure. Crosstab are particular in many respects, and, in particular, they
REQUIRE that the parameters are DECLARED for their data type. You can do it
while in the query designer, from the toolbar, under Query, the last item is
Parameters... Supply each and every parameter needed for the query
(including parameters enclosed in other query, if the query is based on top
of other queries), and their data type.


Next, if you use DoCmd, or Dxxx functions, row source or record source
properties, your reference to FORMS!formName!controlName are automatically
resolved for you, BUT if you use CurrentDb object, or a database object,
this DOES NOT automatically occur for you, and you have to solve the
reference, through code (or otherwise):

Dim q as queryDef : Set q=CurrentDb.QueryDefs("queryName")
Dim p as DAO.parameter
for each p in q.Params
p.value = eval(p.name) ' assuming a name like
FORMS!formName!controlName
next p

Dim rst AS DAO.recordset
set rst=q.OpenRecordset(...options... ) ' q is open through currentDb,
so no automatic
' reference to
Forms!formName!ControlName would have occurred.
' BUT we did it, walking through the
PARAMETERS collection of the
' object q, the object
representation of the query to be 'executed'
' here, by asking it to open a
recordet for us.



Hoping it may help,
Vanderghast, Access MVP