Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Filtering form to display a record only when there are data in the subform

    I have a feeling I'm missing the blindingly obvious, but here goes:

    I have a form with a subform that work fine together, and are used for comparing imported records (in the subform) against existing data (in the main form). However, sometimes there are no data in the subform (ie no imported records matching an existing record), in which case I don't need to see the record in the main form at all.

    In other words, I only want to see the existing records that might be affected by newly-imported data - only about 30% of the overall records.

    Can I filter my main form to show only records that have one or more matches in the subform?

    Thanks

    Alison C

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Yes, I was overlooking the blindingly obvious...

    Sorry to waste your time - sometimes I just need to articulate the problem to start looking at it with new eyes.

    The answer, of course, lies not in the form design but the underlying query. I added a DLookup field to the query for the main form, using the field that links the master- and sub-forms. I told the master form query to look up the field in the sub form query, and only to display the master form records where the result of the DLookup was Not Null.

    Duh.

    Thanks to all of you who had a look at the problem, anyway!

    Cheers

    Alison C

  3. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Alison,

    I don't know that filtering would be so easy, but will you settle for changing the recordsource of your main form? All you need to do is add a subquery that references the table that contains the subform records to the recordsource for your main form. Here is an example that you can follow with the sample Northwind (2003 version) database:

    1.) Open the Categories form in normal view. There are (8) category records. Add one or more category records, without adding any associated products.
    2.) Open the Categories form in design view. Note the recordsource for this form: Categories table.
    3.) Click the build button, to create a new query as the form's recordsource. Add all fields from the Categories table.
    4.) Enter the SQL for a subquery into the Criteria for the CategoryID field: In (SELECT CategoryID FROM Products)

    The complete SQL statement for the Categories form is now this:
    Code:
    SELECT CategoryID, CategoryName, Description, Picture
    FROM Categories
    WHERE CategoryID IN (
        SELECT CategoryID 
        FROM Products)
    Now open your form. Note that you will still only see the 8 records, but you shouldn't see the additional record(s) that you added in step 1, above.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  4. The Following User Says Thank You to tgw7078 For This Useful Post:

    alifrog (2012-06-13)

  5. #4
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    The solution I just posted is similar, but it avoids the use of the Domain Aggregrate function DLookup in the query. Using Domain Aggregrate functions in queries can be performance killers. The subquery will execute much faster.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  6. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Thanks

    Thanks, tgw7078 - yes, I'm well aware from bitter experience that DLookup can be the worst option where a reasonable amount of processing is required!

    In this case, I'm only looking at relatively few records, and this procedure only needs to be run infrequently - it was more a matter of the end user not panicking because of an empty subform - so the quick-and-dirty route is working fine in this instance.

    However, I'll take a look at the SQL method you've posted and store that away for future reference, because it'll probably be far more robust when I need the same feature for a more extensive dataset.

    Cheers

    Alison C

  7. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Oops - just realised you do have a name, Tom - I just couldn't see it when I was writing my response - wasn't trying to be discourteous!

  8. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Thanks again, Tom - it works a treat in my database.

  9. #8
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Oh, geez, no problem! You simply addressed me by my login username--I'm sure you could have called me much worse.
    Last edited by tgw7078; 2012-06-13 at 11:26.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  10. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm closed this thread since people insist on posting messages suggesting the user employ .NET data grids as spam messages.  The user problem has been resolved a coule of months ago.
    Last edited by WendellB; 2012-08-16 at 14:18.
    Wendell

Posting Permissions

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