Using a form control to supply query criteria

[JonWayne]

I have a crosstab query that is dependent on a relatively long list of
queries, one of which has a form-control reference as a criteria value.
Before, there was no criteria required, and all the queries ran fine
(individually, and stacked beneath the final crosstab). Then I modified one
of the intermediate queries (some 4 levels below the final crosstab) so that
it reads a numeric value from an open form into the criteria row of one of
its fields. That is where the problem began. All but the crostab query run
flawlessly, however, the crosstab burps up the error message: The Microsoft
Jet database engine does not recognize 'Forms!frmSelectPeriod!cboYear' as a
valid field name or expression. I ensure that the form is open, and the name
is correct as well as the control name - in fact, the query 1 level below
the crosstab opens fine once the form is open. I had this problem before in
another application and I circumvented it by making the query just before
the crosstab, a MakeTable query, then, base the crosstab on that table.
While that worked, I'm guessing that is not the most efficient way of
getting it to work.

Does anyone here have a better solution? Id appreciate any. Thanks

 

[JonWayne]

By the way, the parametized query is a Union query

 

[Ken Snell [MVP]]

Is the form actually a subform in another form? Are the form name and the
combo box name spelled correctly?

 

[JonWayne]

No, Yes. I found out, however, what the problem was. It was right before my
eyes - all I had to do was read it up in online help. Actually, access' help
is not too good on this topic. I found better material at MSDN. For crosstab
queries that are based on other parametized queries, you must declare the
parameters in the "Query Parameters" dialog, Query menu. In the case of
parameters supplied, as in my case, by a form control, you'd enter the full
reference to the control as the parameter. In any case, the data type of the
parameter is also required in that dialog. I am not sure if this is
optional, but in the crosstab query, it is recommended that the
ColumnHeadings property be set too.