Open and close reports (Access 2000)

[freelance]

Spelling error in subject corrected by HansV to help in future searches

I want to open and close reports for each option in my option box in order to check whether the stock is ok. I have a built n function in the report that opens a mesage box when something is wrong.However i cannot build the opening and closing the reports since the query is based on the option box requiring a choice of the option, and i want to carry it out successively from the first till the last option.Which is the best way to do it ? In my example the code should be under the control "Quick check "but i havent done anything there since a cannot refer to the options

[HansV]

It seems rather superfluous to open and close a report repeatedly - you just want to look at the data. So you should create a new query that groups by afid, and that doesn't refer to the form. Once you have the query working, you can create a (single) new report based on the new query. This will be a useful excercise for you.

[freelance]

Yes, i know but it was impossible for me to redo the query.It is so awfully complicated.
I tried to remove the where cndition by writing customers.afid = 7. i deleted the parameters and the sql, but i am geting a syntax error :

TRANSFORM Sum([order details].Quantity) AS Sum1
SELECT [order details].ProductID, orders.orderid
FROM products INNER JOIN (Customers INNER JOIN (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON Customers.Customerid = orders.customerid) ON products.Productid = [order details].ProductID
WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid WHERE orders.Audit=True AND ((Customers.afid)=7))
GROUP BY [order details].ProductID, orders.orderid, orders.orderid, orders.[required date]
PIVOT orders.SubOrder In (-1,0);

If i succeed with afid = 7 then it will be easier for me to build the single query

[HansV]

Try this

TRANSFORM Sum([order details].Quantity) AS Sum1
SELECT Customers.afid, [order details].ProductID, orders.orderid
FROM products INNER JOIN (Customers INNER JOIN (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON Customers.Customerid = orders.customerid) ON products.Productid = [order details].ProductID
WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM Customers AS t INNER JOIN orders ON t.Customerid = orders.customerid WHERE orders.Audit=True AND t.afid=Customers.afid)))
GROUP BY Customers.afid, [order details].ProductID, orders.orderid
PIVOT orders.SubOrder In (-1,0);

[HansV]

The attached database demonstrates another approach using a second query instead of a subquery. The second query qryMaxID returns the highest OrderID with Audit = True for each afid. The crosstab query uses this query to limit the OrderIDs.

[freelance]

Thank you so much ! I think your second suggestion is simply genial. I will apply it and build the query now. I have some questions regarding the query but i think it is proper to open a separate thread.