When to New Record to Junction Table

[Henry Stockbridge]

Hi,

I need a recommendation when to add a record to a junction table that
complements a many to many relationship. There will be a Contacts
form, and an Interests subform with the parent/child field being the
ContactID.

Table 1 - Contacts (ContactID (PK), First, Last, so on)
Table 2 - ContactsInterests (ContactInterestsID (PK), ContactID (FK),
InterestsID (FK))
Table 3 - Interests (InterestsID (PK), Interests_Description)

If I add a record to the junction table immediately after a new
contact is added, an update query will need to be run when values
change in the Interests subform. If the User selects an existing
Contact from a combo box, an 'Add Interests' button could be made
visible if no records exist in the juction table for that ContactID.
An append query would be run with the current ContactID. I am unsure
which is a better approach, or if this is the wrong approach
altogether.

Any help or recommendations you can make would be appreciated.

Henry

[Allen Browne]

The simplest idea is just to provide a combo in the subform for InterestID.
The user adds a new interest by selecting one in the combo, and can then add
another one on the next row of the (continuous?) subform.

If you have some interests that apply to almost everyone, you may perhaps
want to use the AfterInsert event procedure of the main form to insert that
interest into the junction table. For example, if you had a Yes/No field
named IsDefault in the junction table. This kind of thing:

Private Sub Form_AfterInsert()
Dim strSql as String
strSql = "INSERT INTO ContactsInterests (ContactID, InterestsID)
SELECT " & Me.ContactID & " AS ContactID, InterestsID FROM Interests WHERE
IsDefault = True;"
With dbEngine(0)(0)
.Execute strSql, dbFailOnError
If .RecordAffected 0 Then
Me.Sub1.Form.Requery
End If
End With
End Sub

The AddInterests button seems superfluous. If you want one on the main form,
you could toggle its Visible property in the main form's Current and
AfterInsert events, and the subform's AfterInsert and AfterDelConfirm
events.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Henry Stockbridge" <hs***********@hotmail.comwrote in message
news:6c**********************************@d21g2000 prf.googlegroups.com...

>
I need a recommendation when to add a record to a junction table that
complements a many to many relationship. There will be a Contacts
form, and an Interests subform with the parent/child field being the
ContactID.

Table 1 - Contacts (ContactID (PK), First, Last, so on)
Table 2 - ContactsInterests (ContactInterestsID (PK), ContactID (FK),
InterestsID (FK))
Table 3 - Interests (InterestsID (PK), Interests_Description)

If I add a record to the junction table immediately after a new
contact is added, an update query will need to be run when values
change in the Interests subform. If the User selects an existing
Contact from a combo box, an 'Add Interests' button could be made
visible if no records exist in the juction table for that ContactID.
An append query would be run with the current ContactID. I am unsure
which is a better approach, or if this is the wrong approach
altogether.

Any help or recommendations you can make would be appreciated.

Henry

[Henry Stockbridge]

On Dec 22, 7:16*pm, "Allen Browne" <[e-mail]:

The simplest idea is just to provide a combo in the subform for InterestID..
The user adds a new interest by selecting one in the combo, and can then add
another one on the next row of the (continuous?) subform.

If you have some interests that apply to almost everyone, you may perhaps
want to use the AfterInsert event procedure of the main form to insert that
interest into the junction table. For example, if you had a Yes/No field
named IsDefault in the junction table. This kind of thing:

* * Private Sub Form_AfterInsert()
* * * * Dim strSql as String
* * * * strSql = "INSERT INTO ContactsInterests (ContactID, InterestsID)
SELECT " & Me.ContactID & " AS ContactID, InterestsID FROM Interests WHERE
IsDefault = True;"
* * * * With dbEngine(0)(0)
* * * * * * .Execute strSql, dbFailOnError
* * * * * * If .RecordAffected 0 Then
* * * * * * * * Me.Sub1.Form.Requery
* * * * * * End If
* * * * End With
* * End Sub

The AddInterests button seems superfluous. If you want one on the main form,
you could toggle its Visible property in the main form's Current and
AfterInsert events, and the subform's AfterInsert and AfterDelConfirm
events.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Henry Stockbridge" <[e-mail] in message

news:6c**********************************@d21g2000 prf.googlegroups.com...

I need a recommendation when to add a record to a junction table that
complements a many to many relationship. *There will be a Contacts
form, and an Interests subform with the parent/child field being the
ContactID.

Table 1 - Contacts *(ContactID (PK), First, Last, so on)
Table 2 - ContactsInterests (ContactInterestsID (PK), ContactID (FK),
InterestsID (FK))
Table 3 - Interests (InterestsID (PK), Interests_Description)

If I add a record to the junction table immediately after a new
contact is added, an update query will need to be run when values
change in the Interests subform. *If the User selects an existing
Contact from a combo box, an 'Add Interests' button could be made
visible if no records exist in the juction table for that ContactID.
An append query would be run with the current ContactID. *I am unsure
which is a better approach, or if this is the wrong approach
altogether.

Any help or recommendations you can make would be appreciated.

Henry- Hide quoted text -

- Show quoted text -

Many thanks.