Complicated filter on combo box!

[barryg80]

Hi there,

Maybe this won't be complicated for some of you guys but it is for me!

I am trying to figure out how to get a combo box to filter a form with a many to many relationship in the middle. ERD image attached. The filter is to be applied on the partner field in the clients table with the main form then displaying directors linked to that partner. The in a subform you are shown the income tax details for that director
.


I can do it for a regular one to many relationship by basing the main form on a query of the clients table and then setting the query criteria for the partner field to [forms]![FormName]![cboPartner]. Subform then displays the data relating to that partner.

However with this one there is a M2M relationsip in play using the junction table and I am unsure as to how to get this working. Pointers much appreciated,

Thanks

[ranman256]

If the main filter is working, good. But do you need more filters after that. You can filter the sub form further , maybe using TAB controls?

[barryg80]

Thanks, have looked at that suggestion but its not a method I want, the rest of the application lets the users select using a drop down to filter so want to stick with that. So drop down to select the partner on the main form, i need to figure out how to then have a sub form display the directors relating only to that partner.

[ItsMe]

Have you tried bringing the Key value from your junction table into your combo and the form's recordset?

[barryg80]

Not sure how so ItsMe. Will look at that this evening, thanks..