filter form if no records on subform

[GPM4663]

Hi Everyone,
I've got stuck on something I'm sure is relatively easy. I want to filter a form on opening to display a record only if the record has values in its subform? I hope that makes sense? For example I only want to display a sales header if there are items on the sales line. I don't want to do it through the query because the query is used elsewhere and thought I could do it through the main form filter or during the on open event.

Any help would be greatly appreciated,

Thanks

GPM

 

[MajP]

Likely there is no field in the main table that you could filter. What field would define if a child exists? So I think you are going to have to modify the query, but big deal, copy it and then modify it for the form. One way to modify the original query would be to add a calculated control that counts the child records and sees if the amount of child records is greater than 0. Here is an example:

SELECT
catName,
catID
FROM tblCategory
WHERE DCount("partID","tblPartCat","catID = " & [catID])>0

tblPartCat is a table that has the foriegn key "catID". So for each catID in tblCategory I count the child records in tblPartCat.

 

[TheAceMan1]

How are ya GPM . . .

And if the subform has no records what do you intend to do?
[ol][li]Canxel Opening the form.[/li]
[li]Show all records,[/li][/ol]
What?

Also using query or SQL to find out if subform records exist, is nota problem!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[GPM4663]

Hi,
Thanks for all the input. I only want the form to display header records that have line records associated with it.

MajP,
I'll have a play around with the query to try to get it to work if I can. Is there no way to filter a form using it's filter propery with a DCount("partID","tblPartCat","catID = " & Forms!frmHeader.[catID])>0 type statement?

thanks

GPM