Junction table "lookup"

todavy

Registered User.
Local time
Today, 13:19
Joined
Dec 1, 2009
Messages
17
I have three tables:

1. PRODUCTS
ProductID
ProductName

2. PRODUCT_CATEGORY
Product_CategoryID
ProductID
CategoryID

3. CATEGORIES
CategoryID
CategoryName

I want to make a Form where I can edit one product at the time and on that form have a subform where I can add categories to that product (A product can belong to many categories).

If I use the "make form wizard" on the product table it only results in a subform with PRODUCT_CATEGORY table (wich only shows ProductID and CategoryID). How do I get a subform that that make it possible to change category by the CategoryName and not the CategoryID? (I dont want to use the "lookup function" since it messes with my relations etc.).
 
Change the category field on the subform to a combo box and use the description to select the category but have the id as the bound column.

2 columns
col(1) = CategoryID
Col(2) = CategoryName

Bound column = 1

ColWidths 0,4
 
How do I change the Category field to a combobox if the subform is a basic table?
 
Change the subform to a continious form instead of datasheet.
 
Well this isn't really my cup of tea... Can I change the "subform-table" to a continious form with a click, or should I delete the subform table and insert a new subform? In the last case, how do I make that subform continious?
 
When you design your main form you will see your embedded sub form and the fields on the form as per a single form. Edit the subform and add a combo box by clicking on the id field and go to properties and select change to > combo box. Then use the settings I suggested earlier.

Then go to properties of the subform and select
Datasheet = Yes
Single form = No
 
Thanks for all help so far. I think I'm almost there! I have succeded in changing the categoriID column to a combobox, and I can also make the combobox to display the CategoryName by inserting a SQL statement in the Rowsource. But It now it is trying to store the CategoryName text instead of the CategoryID, and that results in an datatype error. Whats the final touch?
 
Try changing this code
Code:
2 columns
col(1) = CategoryID
Col(2) = CategoryName

Bound column = 1

ColWidths 0,4
to this

Code:
2 columns
col(0) = CategoryID
Col(1) = CategoryName

Bound column = 0
 
ColWidths 0,4
the columns in a combo boxstart from 0 not 1
 
Thanks! Now it works all the way... Case closed!
 
Rabbie

It does when using vba to reference the columns but the properties for the combo box are as is

Bound column = 1
 

Users who are viewing this thread

Back
Top Bottom