2 combo boxes based on each other

[vid6]

Hi!

Basically I'm creating a database in which i have 2 fields called "Product Code" and "Product Name" respectively in one table each with a number of entries.

What i require is that in the form if someone choses a particular Product Name the corresponding Poduct Code should appear on its own and if someone choses a particular Product Code its corresponding Product Name should appear on its own. I'm able to create a one way relationship but I have no idea how to create a 2 way. basically if any one of them is selected the corresponding value of the other one from the Products table should appear on its own. I hope you understand my problem!

Please help.. No idea what to do!!!!

thanks

[Stewart Ross]

Hi. Your design is not yet correct, which is why you are struggling with this issue. You should have a separate Products table which has the product name and all other attributes of the product, all of which will depend on whatever you set as the product key. It is the key for the product which you store in any other table related one-to-many to the products table. You would NEVER store the product name in addition to the key.

We have an insights article on database normalisation and table structures which I think will be essential reading for you. Incorrect design will simply prevent you from developing a successful application.

In terms of selecting products, you can always look up the product key (product code in your application) by presenting users with lists of products or subproducts by name. However, it is just the product key that is carried in to your related table as a secondary or foreign key - not the name of the product.

-Stewart