many to many relationship

[SWM]

How do i set up forms to input information in a many to many relationship. I
have created two tables and a junction table with two id fields and made them
both the primary key. I would like to now input in both tables with the id
automatically showing up in the junction table. Thanks.

 

[Sharkbyte]

One way would be to use code, and your Events. Put a Save button, on your
form, and in the OnClick event add the necessary code to insert the data to
their respective tables.

Sharkbyte

 

[John Vinson]

How do i set up forms to input information in a many to many relationship. I
have created two tables and a junction table with two id fields and made them
both the primary key. I would like to now input in both tables with the id
automatically showing up in the junction table. Thanks.

The simplest way is to pick the most frequently used "one" side table;
base a Form on this table (or a sorted query of the table). Put a
Subform on that form, bound to the junction table. Use the mainform's
Primary Key as the master link field, and the corresponding foreign
key as the Child Link Field of the Subform.

On the subform you'ld have a Combo Box bound to the other foreign key
field. You can put code in this combo's NotInList event to open a data
entry form for the "other" One side table if necessary.

See the Orders form in the Northwind sample database for an example.

John W. Vinson[MVP]

 

[SWM]

What would that code be? Thanks.

SWM

One way would be to use code, and your Events. Put a Save button, on your
form, and in the OnClick event add the necessary code to insert the data to
their respective tables.

Sharkbyte

 

[SWM]

It seems like a good idea, however it only seems to work if referential
integrity is not enforced. Unless i am doing something wrong. Thanks.

SWM

 

[John Vinson]

It seems like a good idea, however it only seems to work if referential
integrity is not enforced. Unless i am doing something wrong. Thanks.

You are doing something wrong, then, because this is an absolutely
universal standard technique! Again - see the Orders form in the
Northwind sample database for an example.

What's the Recordsource property of your Form, and of your Subform?
What are the Master and Child Link Fields?

John W. Vinson[MVP]

 

[SWM]

Thanks for your help. It seems to have started working now, although i dont
know why. However, i need to close and reopen the original form in order for
the record to show up on the list. Thanks.

 

[John Vinson]

Thanks for your help. It seems to have started working now, although i dont
know why. However, i need to close and reopen the original form in order for
the record to show up on the list. Thanks.

I'm confused.

What's the "original form"? Do you have one form for data display, and
a second form with a subform for data entry? What "list"?

John W. Vinson[MVP]

 

[SWM]

Ihave a form-(contacts) with a subform-(junction table) in the subform is my
combo box. in order for an additional record added to the list in the combo
box, i need to reopen the contacts form. thanks again for your time.

 

[John Vinson]

Ihave a form-(contacts) with a subform-(junction table) in the subform is my
combo box. in order for an additional record added to the list in the combo
box, i need to reopen the contacts form. thanks again for your time.

How are you adding the record - in a separate Form, in VBA code, in
the NotInList event of the combo box, or what?

Wherever, you will need to Requery the subform after adding the
record.

John W. Vinson[MVP]

 

[SWM]

How would i requery the subform? Thanks.

How are you adding the record - in a separate Form, in VBA code, in
the NotInList event of the combo box, or what?

Wherever, you will need to Requery the subform after adding the
record.

John W. Vinson[MVP]

 

[John Vinson]

How would i requery the subform? Thanks.

Forms!mainformname!subformname.Form.Requery

John W. Vinson[MVP]