Subform multiple selection

[Modesty_Blaze]

I have, a small problem.
Don't know how to set up the basic thing. TABLES
I have a table "suppliers" and table “goods"
Not all of the suppliers are delivering all of the goods, but all of them are delivering more then one. I want to be able to, when adding supplier to be able to select goods that he is supplying... and believe it or not: I'M STUCK!!!
Help please!!!

 

[Mile-O]

Look at the thread below (at the time of writing) as it has an example of a many-to-many relationship.

 

[Modesty_Blaze]

Got it, but...

I will work on this overnight.

One more thing (If I don't find the answer until tommorow):

How can you auto insert value in subform just by inserting it in the form? (Adding one supplier in supplier form, and comes to the point to chose from the goods that he supplies, that is to fill inn the subform. ) Since supplier in subform is required field and must be the same as on the form (I'll hide it once I know how it works), I don't know how to do that.

Chears,

Modesty

 

[Modesty_Blaze]

Basically

what I'm asking is how to update field in the subform to mach current record? (it should be similar when inserting record as well should it not?)

 

[Pat Hartman]

I posted a many-to-many example that includes forms and shows the relationship from both points of view. You need three tables. A products table, a suppliers table, and what we call a junction table. The junction table identifies which products a supplier supplies. The example db is about bookings but the concept is identical. The bookings example is a little more complex since the same customer can book the same venue more than once so an additional key field, booking date, is required in the junction table. But unless you have some funky rules that require you to know historical information such as SupplierA used to supply this product but doesn't any more, your junction table will only need two columns.
Many-to-Many