A
andrewrubie
Hi All, (Windows XP Pro, Access 2002)
I have a music database with tables holding Recordings and
Artists(tblArtists and tblRecordings). 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).
In the form setup the main frmArtists form is bound to tblArtists, the
subform(Linking fields both 'ArtistID' is bound to the junction table
and the subform has a
combo box with the RowSource (SELECT RecordingID, Title FROM
tblRecordings
returning the Recordings from tblRecordings.
The setup works well for displaying and editing data. However, when a
Recording record
in the subform is selected and the delete key pressed the normal delete
dialog appears alerting the user to the fact 1 record will be deleted.
Yes
is clicked and sure enough 1 record is deleted. This being the record
in the
junction table holding only the ArtistID and RecordingID. Because the
actual
Recording record isn't deleted from tblRecordings it is orphaned. I
guess
when the delete key is pressed 2 records should be deleted, one from
the
junction table and the actual record from the Recordings table. It
doesn't
happen.
Does anyone have any suggestions on how to resolve this?
And of course some Recordings are associated with more than one Artist
so
more than one record will need to be deleted from the junction
table(plus
the main record from tblRecordings).
Any help really appreciated.
Regards,
Penny
Tina: Implemented your suggestions as you'll see above but it doesn't
change
the fact that only the record from the junction table gets deleted.
Thanks
anyway.
I have a music database with tables holding Recordings and
Artists(tblArtists and tblRecordings). 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).
In the form setup the main frmArtists form is bound to tblArtists, the
subform(Linking fields both 'ArtistID' is bound to the junction table
and the subform has a
combo box with the RowSource (SELECT RecordingID, Title FROM
tblRecordings
returning the Recordings from tblRecordings.
The setup works well for displaying and editing data. However, when a
Recording record
in the subform is selected and the delete key pressed the normal delete
dialog appears alerting the user to the fact 1 record will be deleted.
Yes
is clicked and sure enough 1 record is deleted. This being the record
in the
junction table holding only the ArtistID and RecordingID. Because the
actual
Recording record isn't deleted from tblRecordings it is orphaned. I
guess
when the delete key is pressed 2 records should be deleted, one from
the
junction table and the actual record from the Recordings table. It
doesn't
happen.
Does anyone have any suggestions on how to resolve this?
And of course some Recordings are associated with more than one Artist
so
more than one record will need to be deleted from the junction
table(plus
the main record from tblRecordings).
Any help really appreciated.
Regards,
Penny
Tina: Implemented your suggestions as you'll see above but it doesn't
change
the fact that only the record from the junction table gets deleted.
Thanks
anyway.