changing Unbound combo box to bound combo box

[Karl]

I have 3 tables that are linked.

Category Table:
Category ID (primary key) -autonumber
Category Name

Products Table:
Product ID (primary key) - autonumber
Category ID
Product Name

Components Table:
Component ID (primary key) - autonumber
Product ID
Equipment Name
Make
Model

I have created a form (Form1) that has an unbound combo box that lists all
the names of categories for all the products I have. I have a subform (Form
2) with a unbound combo box for products. For this combo box on the products
that are associated with the chosen category are shown. I would like to
create a subform (Form 3) in Form 2 that shows the Component information of
each product selected. My original plan was to simply link these by product
id as a master and child field, however as the product and category combo
boxes are unbound I don’t think this is possible. I tried to make the product
combo box a bound field with the products table the record source and product
name the control source (I also tried to make product id the control source
and it caused my combo box to quit working properly). I then added a text box
with the product id as the control source that would allow me to use as a
master field for the link to the child field (product id on form 3). However
the text box with the product id doesn’t update to the correct product id
number for the product name chosen in the products combo box. Note: I left
the category box unbound.

I was wondering why this isn’t working or if there is a better method for
connecting subform 2 to subform 3.

Thanks a lot,
Karl Zimmer

 

[G. Vaught]

To have the subform 3 link to subform 2, you must use subform 2's primary
key as a field in subform3, which would then become the foreign key. Then
subform 2 would be linked into the main form. Subform 2 needs the primary
key from the main form.

If you create your forms correctly, when you pick the category, it should
automatically pick all related values for the other forms.

 

[Karl]

Hello,
Thanks for you reply.
Yes I have done that however. Both combo boxes are unbound so they won't
allow the subforms to link. When I choose a record source for subform 2 and
then add a text box that shows the primary key. This is fine with regards to
linking to subform 3. However the problem encoutered is the primary key on
subform 2 isn't chaning to the appropriate value when a product is selected
from the combo box in subform two. So then of course subform 3 doesn't update
either due to the fact the primary key that its linked to in subform 2 hasn't
updated either. I have tried numerous approaches and nothing has yet to work.

Any further suggestions?

Thank you,
Karl