Many-to-Many lookup

todavy

Registered User.
Local time
Today, 13:20
Joined
Dec 1, 2009
Messages
17
In Access 2007 I have a Many-To-Many relation set up between Products and Categories (with a Products_Categories_Spec junctiontable between), so one product can have many categories and one category can belong to many products.

I'd like to make a Form where I can easily set the categories for each product without having to deal with the ID numbers, which is the case when just using the "make Form Wizard".

How is this possible without using the "evil" lookup function?
 
It's okay to use lookups (using combo boxes) on the FORM (just not at table level).

You can do this either by:

1. Put the lookups in the table TEMPORARILY so then the form wizard will create the form with combo boxes all done.

OR

2. Create the form and then go convert the text boxes for each of the two fields to combo boxes manually (FORMAT > CHANGE TO > COMBO BOX) and then manually configure - add row source, change column count, change column width properties.
 
2. Create the form and then go convert the text boxes for each of the two fields to combo boxes manually (FORMAT > CHANGE TO > COMBO BOX) and then manually configure - add row source, change column count, change column width properties.

What text boxes? When I open the Products table and click make form, the Products_Categories_Spec table is inserted in a child form as a table with ID numers.
 
It's okay to use lookups (using combo boxes) on the FORM (just not at table level).

You can do this either by:

1. Put the lookups in the table TEMPORARILY so then the form wizard will create the form with combo boxes all done.

Yes I tryed making a temporarily lookup and then the form wizard, and it worked AS LONG AS THE LOOKUP REMAINED. When I removed it from the Product table it wes no longer in the form. By the way, the form wizard just put the Categories table in the subform and didnt do any combo box at all.
 
Yes I tryed making a temporarily lookup and then the form wizard, and it worked AS LONG AS THE LOOKUP REMAINED. When I removed it from the Product table it wes no longer in the form. By the way, the form wizard just put the Categories table in the subform and didnt do any combo box at all.
Removing the lookup wouldn't do anything to the form. How did you remove the lookup from the table?
 
Removing the lookup wouldn't do anything to the form. How did you remove the lookup from the table?

Well first I do the lookup on the CategoryID field at the junctiontable (Products_Categories_Spec). Then, when clicking the "+" sign at the left side on each post in the Products table the Categories ar opened in a sub table and I can add as many categories I like. So far so good.

While still having the product table activated, I go to the formwizard, add all fields from the product table, add all fields from the Products_Categories_Spec table, choose the option to "view per Product table" and click Finnish. Then I end up with a form with the Product info in the main form and a subform with the Products_Categories_Spec table (not a subform-form but a table). Then I go to the Products_Categories_Spec table and delete the lookup (change the "Show control" property from "combination" to "textfield" at the Lookup tab).

Then I open the new form I made and voilá! ...the lookupfield is preserved... hehe.. I guess I did everything righ this time then.. So this means that the lookup function is converted to a more safe combo box then? Thanks for the help! :)
 

Users who are viewing this thread

Back
Top Bottom