Using Intermediate Table on Form (1 Viewer)

[tmyers]

On a form I am trying to complete, I have 2 tables (tblFixtureType and tblDrawings). I have an intermediate table tblDrawingFixtureType tying the two together.

On my form I have a text box to enter a drawing and a combobox to select a drawing entered. I then put that intermediate table as a subform on that form. It is correctly pulling DrawingID (and even changing with the combobox like I wanted). The problem is that it isnt populating with the TypeID from tblFixtureType.

On another part of the form (this is all contained in a tab control), I have types being inputted. It is just a basic sub form of a the table in datasheet view.

How can I get the intermediate table/form to show the whole list of types, but change what drawing ID the quantity is being enter on as the combo boxes selection changes?

Eventually I would hide the DrawingID, as it isn't relevant to the user, but how could I have that form show the entire list of types entered no matter what the combo box has selected? Essentially I want the types to remain static, but the DrawingID and Quantity to change with the combo box. The list of types wouldn't deviate from the other datasheet once they are entered, but each type can have different quantities per DrawingID.

Here is my table structure for the three tables involved.

 

[tmyers]

After playing with this more, I believe I can't just make a form purely based off the table. I need the sub form to be driven by a query tying the two tables together through the intermediate table with master/child links.

I tried a couple different queries, but still wasn't able to get it to work.

 

[tmyers]

Managed to solve this.
Changed TypeID to a combobox and did a query for it. Changed its columns to contain typeid and typename. Changed widths to only show typename.

Now the subform allows you to pick type via a drop down, and cycles based on the drawings combobox selection.

 

[Pat Hartman]

Here's a sample that shows how many-to-many relationships are handled. Your subform is bound to the junction table. The master/child links provide one of the "many" foreign keys and a combo provides the other. You seem to have happened on the solution but do take a look at the sample anyway.