combobox across 3 tables

[ben1234zz]

Hi

We the table structure below:

SM_Products.ProductID >> SM_Prod_Cat_Link.ProductID
SM_Prod_Cat_Link.CategoryID >> SM_Prod_Categories.ID


Basically for each product you can have many categories, therefore the SM_Prod_Cat_Link table.

My question is: on my product form how can I select the categories for that product.

I assume I add a subform, which I have done with the below setup, however when I select a category it just disappears in the combo box.

How can I do this?

Thank you in advance

B

 

[MajP]

build the subform using a query that links
SM_Prod_Cat_Link linked to SM_Prod_Categories

set up the child master between Prod ID in the main form and
SM_Prod_Cat_Link.ProductID in the sub form.

Now you need a combo box on the subform in the Cat ID field that displays the category but saves the ID.

 

[Guest_imported]

This same situation comes up where you have a list of states and cities in those states... or products and categories.

You can do it with two combo boxes (a subform isn't necessary) by simply setting up a query that filters its own records based on the other combo box... then requery the second combo box.

I actually have a free tutorial on my web site that explains how to do this:

http://599cd.com/tips/access/two-combo-boxes?key=TekTips

Unless I misunderstand your question.

 

[MajP]

Although the OP never responded, I am pretty sure you misunderstood. The OP seems to be asking how to set up a form subform to handle a many to many relationship as they describe their linking table.

 

[ben1234zz]

Hi

Thank you all for your posts.

Just to clarify, the question is how to handle a combo box in a subform for a many to many.

I have a subform for: SM_Prod_Cat_Link which is working correctly.

I have attempted to convert the SM_Prod_Cat_Link.CategoryID textbox into a combobox, but once I change the setup to the below it goes blank:

Row source: SELECT [Name], ID FROM SM_Prod_Categories
Bound Column: 2

Should this not work?

Many thanks for your posts.

B