Sub Subform displaying associated items..?

[briananderson]

I'm somewhat green on this kind of stuff. What I have is a catalog of film titles. Each one of those titles have multiple distribution agreements. Each one of those agreements have different territories. So I have three tables: Titles, Contracts, and Territories, each one has their own ID# and are linked together Titles linked to Contracts. Contracts linked to Territories (one to many).

I'm working on a form right now where the user can select a title and a list of contracts will be displayed. What I want then is the user to be able to select one of those contracts and (either a new list below or popup) get a list of all the territories associated with that contract. I've been trying a few different way. I can get the Title selection combobox to display all the correct contracts, but when I want to get a list of territories by using a popup form, it lists all the territories in the table and not just the ones associated with the contract. I know this is probably something easy, but I just don't quite know how to attack this.

Your help would be much appreciated!

Thanks!

 

[mithani]

See if this might help you.

Write this select statment in After Update even in contactfield.

Me.territories.RowSource = "SELECT TerritoryTable.Territory FROM TerrirotyTable WHERE Contact = '" & Me.ContactField & "'"