Combo Box

[Nick Bradbury]

Hi

I have a form with a combo box to select stock items, my problem is that the
items have an eleven character code, the first seven characters identify
the product and the last four characters identify the supplier and country
of origin. Consequently there are several options to choose from and
depending on what is available I need to be able to select different
products. What I want to achieve is to have one combo box to select the
first seven characters, which stay constant, and once this selection is made
a second combo box containing the list of alternatives for that product.

Is this possible and if so do I need to create separate tables for the seven
character codes and eleven charcter codes or can they exist as separate
fields in the same table


Thanks


Nick

 

[Darryl Kerkeslager]

The two fields represent different objects, where the product (1st 7 char)
is related to the supplier (last 4 char), and in order to do what you want.
they should be in two separate tables. Based on your description, the
product table will be one table and your seven characters will be the
product_code. Presumably the product table will have the product
description as well. The four characters will be the supplier_code field of
the supplier table, which I assume would also have the name/address of the
supplier.

The exact type of relationship will depend on your data, but usually a
supplier can have more than one product, so you would have a many-to-many
relationship.

I would caution you to not use the product_code as the primary key in the
product table if the product code is assigned by someone external to you,
since they could change the product code on you, and hose all your
relationships.