Junction Tables

[patkeaveney]

I have created a junction table in my database.the tables being linked are tblplayerInfo to tblGuardianInfo. The junction table containd the PK of both these tables.
A player can have more than one guardian (Mum,Dad, etc) and a Guardian can have more than one player (two sons, etc)
On my form i display both sets of info. The player info in the main form and the guardian info in a subform.
I want to be able to add a new guardian to a player.
How do I update the junction table to reflect the new guardian addition, or does Access automatically create the record in the Junction Table (if so How)

 

[ScottGem]

The subform should be bound to the mainform on PlayerID. All you need onthe subform is a combobox that allows you to select a Guardian.

 

[patkeaveney]

Thanks for that Scott, but I am trying to add a New Guardian, so at present that Guardian name will not apper in a combo Box.
A Player Record must exist for a Guardian record to be addded, so I have placed an Add New Guradian command button on this form.
Is this the best way to add these Guardian Records?

 

[ScottGem]

Ahh, if you are trying to add a new Guardian, that's a different story. Yes, you cannot add a new guardian into the subform. You can either add a button to open the new Guardian form, or use the NotInList event to do it.

 

[patkeaveney]

Thanks again Scott.
If I add a button to open the New Guardian form from the player form how does the Junction table record get created.
Sorry If I am being daft here, I have never used Junction tables before.
Thanks in advance

 

[ScottGem]

A junction table is used in a many to many relation. Generally records are added to the Junction table from a subform that is bound to one of the tables being joined. In such a case you use a combobox to select a value from the other table. If you are adding records in a single form, then you would have 2 combos, to select a record from each table.

If you need to add a new record to one of the tables being joined, then you need to open a form bound to that table. One the record has been added, you then requery the combobox so you can select the new record.