Linking Field between Form and Subform gets deleted

[Penny]

Hi All, (Windows XP Pro, Access 2002)

I have a music database with tables holding Recordings and Artists. It also
has ‘tblLINKArtist_Recording’ making up the many to many relationship as any
given recording can have many associated artists(with two fields: RecordingID
and ArtistID). Mostly works well.

It has an ‘frmArtists’ which shows the artist details and it intern has a
subform which lists all the recordings associated with that artist. The
subforms Link Master and Link Child fields are on ‘ArtistID’. The subforms
record source is a query as follows:

SELECT tblRecordings.RecordingID, tblRecordings.Title, tblRecordings.Label,
tblLINKArtist_Recording.RecordingID, tblLINKArtist_Recording.ArtistID,
FROM tblRecordings INNER JOIN tblLINKArtist_Recording ON
tblRecordings.RecordingID = tblLINKArtist_Recording.RecordingID;

PROBLEM: when I select an associated recording in the subform and select the
row selector cell, then press delete, I get the usual “You are about to
delete 1 record(s)†alert, I respond ‘Yes’ and the recording disappears.
Seems okay but I can look up that recording in the recordings form or table
and it seems the recording wasn’t deleted at all. however, there is now no
artist associated with it, it has been orphaned. It seems the opposite to
what I wanted has occurred.

Can anyone give any ideas on how to have it delete the recording in the
subform and not just the link between the recording and the artist?

Regards,

Penny.

 

[tina]

so you have 3 tables: tblRecordings, tblArtists, and
tblLinkArtist_Recording. the third table is a "linking" or "junction" table
between the other two tables. you have a main form bound to tblArtists, with
a subform where you want to show all the recordings that belong to that
artist. if all of the preceding is correct, then:

bind your subform to tblLinkArtist_Recording, *not* to a multi-table query.
in the subform, create a combo box control, and set its' ControlSource
property to RecordingID. set the combo box's RowSource property to the
following SQL statement, as

SELECT RecordingID, Title, FROM tblRecordings;

set the ColumnCount property to 2. set the ColumnWidth property to 0"; 2"
(if 2 inches is not wide enough for the recording titles, or is too wide,
then adjust the width of that column).

the above is a standard setup for two "parent" tables linked via a "child"
junction table. to reduce it to a "general" guideline: one parent table
bound to main form, junction table bound to subform; combo box in subform
with RowSource using other "parent" table.

hth