Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Blank Records in Form

    I have a form (Orders) created from a query which just gives me order numbers. I have a subform (details), from another query which gives me the order details. I have a criteria on this query, which gives me only certain orders. The problem is, because I have the form and subform linked up on the order number, the main form still displays an order number it finds in the main query, without details, because there is no match in the subform. How can I prevent it from displays these orders. I though I knew how to do this, but obviously not.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Can the first query use the same criteria as the second query?
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Not really. There's only the Order No in the query for the main form that's relevant. I did put more fields in the query to filter the records, but where say an order had 3 line entries, as well as displaying the 3 line entries on the sub form, the main form had 3 copies of the order.
    Last edited by robm; 2011-03-17 at 05:18.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by robm View Post
    Not really. There's only the Order No in the query for the main form that's relevant. I did put more fields in the query to filter the records, but where say an order had 3 line entries, as well as displaying the 3 line entries on the sub form, the main form had 3 copies of the order.
    You can add the extra fields to the query grid, but not tick the Display tickbox, and use them for setting criteria. Then to remove the duplicate lines display the Properties of the Query and set the Unique Values Property to Yes.UniqueValues.gif

    Another option is to use a Subquery. Let's say Query2 is the one used by the subquery. Query1 is the query for the main form.
    Query1 currently says something like:
    "Select tblOrders.* from tblOders"
    change it to:
    "Select tblOrders.* from tblOrders where tblOrders.OrderID in (Select Query2.OrderID from Query2)
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John. That worked.

Posting Permissions

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