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

[alifrog]

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

[alifrog]

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

[tgw7078]

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:

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.

[tgw7078]

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.

[alifrog]

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

[alifrog]

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!

[alifrog]

Thanks again, Tom - it works a treat in my database.

[tgw7078]

Oh, geez, no problem! You simply addressed me by my login username--I'm sure you could have called me much worse.

[WendellB]

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.