Filtering Data in a Subform for a form

[mchadwick]

I have a form with a subform I am using to verify the data somewhere else (Active Directory). The form shows the group name and the subform is showing the LANID. I need to modify the data in AD and it works fine the way it is other than the fact that every record for each group is in the form.

Example:
FormRecord1 "Wireless"
Subform for Record1 (linked) "Bob Jones"

FormRecord2 "Bluecoat"
Subform for Record2 (linked) no records

FormRecord3 "Exchange"
Subform for Record2 (linked) no records

FormRecord4 "Financial"
Subform for Record4 (linked) no records

FormRecord5 "Petty Cash"
Subform for Record5 (linked) "Bob Jones"

The only records for the Form I want to see are the ones that have records in the subform. Therefore, the Form records that would show up are FormRecord1 and 5 because they are the only records that have data in the subform. I have over 200 records in the Form, which basicaly means when I go through this process I have to go through each record until I get to a record that has data in the subform.

[June7]

Could have an unbound combobox or listbox on main form with a RowSource that lists only the groups that have associated LANID records. Then navigate the main form by selecting from the list to go to the relevant record - need code (macro or VBA).

Another approach is to have the main form RecordSource filtered to only records with associated LANID records:

SELECT * FROM Groups WHERE GroupID IN (SELECT DISTINCT GroupID FROM LANIDrelatedtable);