When to New Record to Junction Table

When to New Record to Junction Table

am 23.12.2007 01:22:06 von 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

Re: When to New Record to Junction Table

am 23.12.2007 02:16:16 von 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" wrote in message
news:6ce9ccb7-7c3a-4d0d-993e-f8a5c8858f13@d21g2000prf.google groups.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

Re: When to New Record to Junction Table

am 23.12.2007 02:44:14 von Henry Stockbridge

On Dec 22, 7:16=A0pm, "Allen Browne" wrote:
> 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 a=
dd
> 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 tha=
t
> interest into the junction table. For example, if you had a Yes/No field
> named IsDefault in the junction table. This kind of thing:
>
> =A0 =A0 Private Sub Form_AfterInsert()
> =A0 =A0 =A0 =A0 Dim strSql as String
> =A0 =A0 =A0 =A0 strSql =3D "INSERT INTO ContactsInterests (ContactID, Inte=
restsID)
> SELECT " & Me.ContactID & " AS ContactID, InterestsID FROM Interests WHERE=

> IsDefault =3D True;"
> =A0 =A0 =A0 =A0 With dbEngine(0)(0)
> =A0 =A0 =A0 =A0 =A0 =A0 .Execute strSql, dbFailOnError
> =A0 =A0 =A0 =A0 =A0 =A0 If .RecordAffected > 0 Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Me.Sub1.Form.Requery
> =A0 =A0 =A0 =A0 =A0 =A0 End If
> =A0 =A0 =A0 =A0 End With
> =A0 =A0 End Sub
>
> The AddInterests button seems superfluous. If you want one on the main for=
m,
> 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. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Henry Stockbridge" wrote in message
>
> news:6ce9ccb7-7c3a-4d0d-993e-f8a5c8858f13@d21g2000prf.google groups.com...
>
>
>
>
>
> > I need a recommendation when to add a record to a junction table that
> > complements a many to many relationship. =A0There will be a Contacts
> > form, and an Interests subform with the parent/child field being the
> > ContactID.
>
> > Table 1 - Contacts =A0(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. =A0If 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. =A0I 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.