Many 2 many - joining table doesn't work

[GReg]

I have 3 tables:

orders
---------------
order_id
date
notes
price

sets
---------
set_id
order_id
item_id

items
---------
item_id
item_name

The idea is to have many orders with many items, with items selectable
from combo box. Orders table should be dynamic, and filled from form. I
can't make the middle table work. The problem is that neither order_id
nor item_id is being saved there.
I tried few examples and tutorials and nothing works.
What can it be?

GReg

 

[Roger Carlson]

If you can stand one more example: On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"ImplementingM2MRelationship.mdb" which illustrates two ways to do this
(depending on your circumstances).

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

The idea is to have many orders with many items, with items selectable
from combo box. Orders table should be dynamic, and filled from form. I
can't make the middle table work. The problem is that neither order_id
nor item_id is being saved there.
I tried few examples and tutorials and nothing works.
What can it be?

Probably you're doing something wrong with the form design. Since you
haven't described what you're doing, and we can't see your screen,
it's more than a bit difficult to say what!

Normally one would have a Form based on Orders, with a continuous
Subform based on the middle table; this would use Order_ID as the
master/child link field and have a combo box storing the Item_ID.

Have you looked at the Orders form in the Northwind sample database
which comes for free with every installation of Access?

John W. Vinson[MVP]

 

[GReg]

If you can stand one more example: On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"ImplementingM2MRelationship.mdb" which illustrates two ways to do this
(depending on your circumstances).

This is one of the examples I was talking about. The case I'm
interested in is 2. I just cannot make it work. It's pretty similar: I
need combo with product names only. But when I take them from products
table it cannot save that in sets, because it is text not number. I
have the joins on tables as you, you didn't use any queries that I see
= should work.
I tried your way- to choose product_id from combo and display names in
text box next to it but it shows number not the name.
I think I should read more about working with access before trying to
do something, though it looks pretty simple.

THX
GReg

 

[John Vinson]

This is one of the examples I was talking about. The case I'm
interested in is 2. I just cannot make it work. It's pretty similar: I
need combo with product names only. But when I take them from products
table it cannot save that in sets, because it is text not number. I
have the joins on tables as you, you didn't use any queries that I see
= should work.
I tried your way- to choose product_id from combo and display names in
text box next to it but it shows number not the name.
I think I should read more about working with access before trying to
do something, though it looks pretty simple.

It's always good to read about the program you'll be using before
diving in... <g>

A Combo Box has three relevant properties: the Row Source (defines
what data is in the combo, in this case the ProductID and the
ProductName); the Bound Column, what actually gets stored in the table
(in this case it would be 1 if the ProductID is the first field in the
row source query); and the ColumnWidths property. This is a string of
numbers separated by semicolons; if you use

0;1.5

then the ProductID (the value to be stored) will be concealed from the
user's view, and the product name will be displayed.

John W. Vinson[MVP]