Many to Many junction table and subforms

[Sammie]

I have a junction table with 2 fields: MainProduct and SubProduct. 1
MainProduct can have many SubProducts, and 1 SubProduct can have many
MainProducts. The parent Product form contains fields ProductID,
ProductName, MainProduct and SubProduct. I want to put 2 subforms on the
parent form showing both the Main and Sub related products.

My problem is the ProductName combo box. It is bound to the ProductID with
the ProductName field also, and formatted to hide the ProductID field. This
same combo box appears on the parent and also on the subforms. On the
subform, this combo box value is stored in the MainProduct or SubProduct
field, with the idea that it will add a record to the junction table. I
can't consistently get the ProductName to display in the subforms. Either
the product ID or a blank appears instead of the productName.

Since my subforms are opposites, I copied one subform and simply reversed
the fields. So on the MainProduct subform, the MainProduct is in the form
header, and vice versa for the SubProduct subform. As soon as I move the
ProductName combo box to the form detail section, it no longer shows the
product name (shows either ProductID or blank - I have switched it around
every way I can think of but can't get it to work).

My subforms are linked to the parent on two different fields: MainProduct
and SubProduct. I have the same ProductName combo box for these fields.

Maybe I can't depict both of these relationships on the same main form for
some reason. It's driving me crazy. I hope someone can help.

 

[Crystal (strive4peace)]

Hi Sammie,

before we discuss the forms, lets make sure your tables are properly
structured and the relationships are good.

"The parent Product form contains fields ProductID,
ProductName, ..."

The form should not have BOTH ID and the name -- the name can be dervied
from the ID anytime. It is best to base each form on just one table.

"I have a junction table with 2 fields: MainProduct and SubProduct"

I would be inclined to name these fields:

ProductID
ProductID_Sub

also, I do not know how your products are set up, but I often relate a
table back onto itself. Can you give some examples of products and sub
products?

Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
have an awesome day
*