Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open and close reports (Access 2000)

    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
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Open and close reports (Access 2000)

    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.

  4. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open and close reports (Access 2000)

    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

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Open and close reports (Access 2000)

    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);

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Open and close reports (Access 2000)

    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.
    Attached Files Attached Files

  7. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open and close reports (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •