Subforms for many to many relationships

[Kostas]

Hello,

I am trying to create a subform for many to many relationships.
Here is the example:
3 tables, a) SONG b) STYLE c) SONGSTYLEJUNCTION
One song may have many style, one style characterizes many songs, and the
junction holds the appropriate matchings.
I would like to enter Styles for a Song on the Song's Form.

First qs: Am I building the subforms the right way?
I create a form for the SONG table, that includes a subform of the Junction
table (which is the Junction table's form), which in turn includes a subform
of the STYLE table (the form of the STYLE table).

Second qs: So far so good, but what happens if I want to enter a new style
for one song? (One that does not already exist in the STYLE table). I get an
error when I try this.

Third qs: Any good links tutorials on creating subforms for many to many
relations?

Many thanks,

Kostas

 

[David Mann]

Hello,

I am trying to create a subform for many to many relationships.
Here is the example:
3 tables, a) SONG b) STYLE c) SONGSTYLEJUNCTION
One song may have many style, one style characterizes many songs, and the
junction holds the appropriate matchings.
I would like to enter Styles for a Song on the Song's Form.

First qs: Am I building the subforms the right way?
I create a form for the SONG table, that includes a subform of the Junction
table (which is the Junction table's form), which in turn includes a subform
of the STYLE table (the form of the STYLE table).

Second qs: So far so good, but what happens if I want to enter a new style
for one song? (One that does not already exist in the STYLE table). I get an
error when I try this.

Third qs: Any good links tutorials on creating subforms for many to many
relations?

Here is a suggestion:
Make a form based on your SONGSTYLEJUNCTION table, displayed as continuous
forms.
Use a combo box for the [song] field looking up values from the SONG table.
Ditto for [style] field, STYLE table,

Add code to the [On Not in List] event of each combo box to add the new
value to the appropriate table,
and to requery the combo box so that the new value can then be selected.

If you want, the [On Not in List] code can open a form for the SONG or STYLE
table so that you can enter any other info required as well as adding the
name.

David

 

[Kostas]

Thanks for the tip, appreciated

Kostas