SubQuery not recognizing criteria reference to a cell on a form

[Historypaul]

I'm trying to run a query on the click of a button. The query is dependent on a couple subqueries. Two of the subqueries have one criteria, which is in a cell on an open form. [Forms]![Project_Info].[ID]


If the form is open, I can run the subqueries individually and they recognize the data reference just fine, but if I try to run the main query, when it calls the subquery (it seems), I get a "Microsoft Access database engine does not recognize '[Forms]![Project_Info].[ID]' as a valid field name or expression." Am I missing something here?

[RuralGuy]

The Project_Info form is still open, right?

[Historypaul]

Yes. I've kept it open during each test

[RuralGuy]

The syntax looks right and should work.

[Historypaul]

Yes... It's vexing me.

[June7]

Can't replicate issue. Do you want to provide db for analysis? Follow instructions at bottom of my post.

[RuralGuy]

When something that should work doesn't, I start looking for corruption. Try *importing* your db into a new fresh db and see if the issue follows. http://www.btabdevelopment.com/ts/impnew

[Historypaul]

OK, so I've narrowed it down a little bit. Part of the problem seems to be that one of my subqueries is a crosstab subquery. It seems whether I try to apply the criteria to the subquery of the crosstab query or to the query that joins the cross tab query to another, I get this error message. I really need to be able to filter the subquery of the crosstab query by this criteria otherwise, I get a ton of extraneous data.

I'm reluctant to send the db, June, because it's huge. It would be quite a task to separate this component from it.

[RuralGuy]

It is pretty easy to limit what is in your db by importing into a new db and only selecting what is needed for the import.

[Historypaul]

OK... I've been researching and it looks like the problem is simply that I can't run a crosstab query on a subquery that has filter criteria that it has to look up. Apparently, the crosstab query doesn't know how many columns it will have to have if I'm trying to filter its subquery with a reference. What I did was make the subquery of the crosstab query a "make table" query. I run the make table query with a vba command, then run the main query, which runs the crosstab query off of the new table and combines it with the data from another table and voila... it works. It seems to run pretty efficiently, too, since all of my data handling is still being handled by queries.

My only fear is that eventually, I'm splitting this thing up and running the back end on a SQL server. I hope this doesn't screw that up.

[RuralGuy]

Thanks for the update.

[June7]

I don't have much occasion to use crosstab but I have read they deal with dynamic parameters differently. See if this helps http://allenbrowne.com/ser-67.html

[onechriswhite]

I had the same problem. Converted to MakeTable - Joy! Now also I can use the same crosstab query for all sorts of different filters, just re-populate the table accordingly. It's genius. Thanks.