Solved Form/Query/Subquery Not working (1 Viewer)

[wmix]

Hello Everyone,
I am hoping this is a simple solution and it will be one of those DUH moments for me. (using Access 2000)

Here's what I have.

Query # 1
Finds information based on 3 different tables using MachineID
I have been manually adding MachineID then I save the query.

Query # 2
Based on Query # 1 I get sums based on year (ProductSold, ProductExpired) by ProductName.

So these two queries work great, as long as I manually save the MachineID in Query # 1.

I decided to create a form (frmMachineProductTotals) to collect the MachineID from the user and run Query # 1 and subsequently Query # 2.

I passed MachineID using the following from the form to Query # 1:
[Forms]![frmMachineProductTotals]![MachineID]

This works. So I left the form and Query # 1 open and tried to run Query # 2 to see if it would work, no luck, I get this error:

"The Microsoft Jet database engine does not recognize '[Forms]![frmMachineProductTotals]![MachineID] as a valid field name or expression"

I know in the past I've written a query based on another query that gets information from a form. So I must be missing something, I just for the life of me cannot think of what it is. :banghead:

Ideas? Thank you in advance!

 

[theDBguy]

Hi. Can you post the SQL statement for Query #1? Thanks.

 

[Micron]

So I left the form and Query # 1 open and tried to run Query # 2

There is no reason to have any query open when it's nested or based on a form. If need be, the form will call/open a query, as will any query nested (chained) to another. You open the last (e.g. 3rd) query in the chain (or again, just open the form based on it) which will call 2 which will call 1, none of which will actually open. Having the query open may be your problem.

 

[wmix]

Well, I figured out the problem. Query 2 is a crosstab query and I didn't have the parameters set, I added the following to both queries and now it works. Thanks for your replies...looking at my SQL helped!

PARAMETERS [Forms]![frmMachineProductTotals]![MachineID] Short;