How to hide records in mainform when subform is empty?

[lvivianl]

I have two tables - one for Customer with a unique customer_ID as the primary key, and another one for Order. Regret to say many of the customers in my contact list haven't yet made any orders. When I create a form having Customer in the main form and Order in the subform, it always include all the records from the Customer table. Is it possible to create a Form with sub-form only showing records when subform is not blank? Please note I have already set the "Join Type" in relationship to Option 3, i.e. include all records from Orders and only records from Customers when the joined fields are equal, and I can also see the "right outer join arrow" in the relationship diagram. Whatelse have I missed

[June7]

Setting relationships does not dictate records retrieved by forms. Need to apply a filter to the main form that excludes customers without orders. This will be tricky because customarily child does not control parent. Do you want the main form record to still be editable?

Have to query the Order table to determine if records for customer exist. Options are a nested subquery or DLookup (or DCount) in the form RecordSource. Like:

SELECT * FROM Customer WHERE Not DLookup("CustomerID","Order","CustomerID=" & [CustomerID]) Is Null);