Choosing table from option group

[Pam Hisaw]

I have a database used for quoting jobs for repair. I have a combo box on a
subform that is on my main form. The combo box is a part number box that
pulls from a parts table. The problem is that the manufacturers
occassionally share the same part numbers. Ex: Company A has part 123
priced at $1.00. Company B has part 123 priced at $1.50. They are both on
one table and I can't index my part numbers because they are shared.

What I would like to do is have my parts on different tables by
manufacturer. Company A on one table and Company B on another table. Then
I want to have an option group so the user can select which company they
need and have the combo box pull only from the table of the manufacturer
selected.

I'm not sure how to write the code to make this happen. Any help would be
greatly appreciated!!

Thanks,
Pam

 

[Van T. Dinh]

It is not recommended to have 2 or more Tables of similar structure
containing similar sets of data.

You can use a set of cascaded ComboBoxes where the user selects a Company in
ComboBox1 and the ComobBox2 will be filtered to only Parts that are supplied
by the Company selected in ComboBox1.

See The Access Web for explanation & sample code:

http://www.mvps.org/access/forms/frm0028.htm