Delete from Linked Subform in Many to Many

[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.

 

[tina]

you're looking at it backward, hon. tblRecordings is one of the two *parent*
tables (tblArtists is the other parent table), and tblLINKArtist_Recording
is the *child* table in its' relationship with each of the other tables.

parent records cannot be "orphaned", only child records can be - when
referential integrity is not enforced in the relationships, that is. so if
you delete a record from tblLINKArtist_Recording, then neither *parent*
record is deleted from the parent tables. that is correct and normal.

as you said, your subform is bound to the linking (child) table. if you want
to delete a record from tblRecordings (parent), then you'll have to create a
form bound to tblRecordings, or come up with some other way to tell Access
to delete a record from the parent table. if CascadeDelete is set up between
tblRecordings and tblLINKArtist_Recording, then deleting a recording record
will cause all associated records in tblLINKArtist_Recording to be
automatically deleted as well; and, of course, this will not affect any of
the records in tblArtists (the other parent table).

hth

 

[andrewrubie]

Hi Tina,

You're right - of course. I was looking at it backwards but now I
understand you clearly. I had implemented it following your
suggestions and like the way you've simplified it.

I've tried a way of deleting the parent record:

In the 'on delete' event of the subform(bound to the junction table) I
coded it to run an SQL delete query that uses the currently selected
records' RecordingID to delete that record from tblRecordings. It does
the job BUT if the user clicks 'No' in the 'you are about to delete 1
record(s)' box then Access seems to have gone ahead and deleted it
anyway. Not desirable. Code below.

Is there a way to interrogate the delete dialog and reverse it's
action(I notice the 'on delete' event can be cancelled but I need
to find out which button was clicked to be able to do anything
meaningful).

Any ideas?

Regards,

Penny

Private Sub Form_Delete(Cancel As Integer)

On Error GoTo HandleErrors

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblRecordings WHERE RecordingID= "
Me.RecordingID
DoCmd.SetWarnings True
Me.Refresh

ExitHere:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub

HandleErrors:
Resume ExitHere

End Sub

 

[tina]

since you're deleting the child record which contains the foreign key value
that will identify the parent recording record you want to delete, you'll
need to store that value before deleting the child record. suggest you
create a module-level variable (i'll call it intRecID) and set it's value in
the subform's Delete event, as

intRecID = Me!RecordingID

then move the "delete the parent record" code to the subform's
AfterDelConfirm event procedure, as

Private Sub Form_AfterDelConfirm(Status As Integer)

If Status = acDeleteOK Then
CurrentDb.Execute "DELETE * FROM tblRecordings " _
" WHERE RecordingID=" & intRecID, dbFailOnError
End If

End Sub

so if the child record is actually deleted from the subform, then the parent
record will be deleted from the parent table immediately afterward. suggest
you also requery the subform's combo box control (where the user picks the
recording for each record), as the last action "inside" the If statement -
so that the deleted recording will be removed from the combo box droplist.

i haven't tested this solution, so give it a go and post back if problems,
and we'll work on it.

hth

 

[Penny]

Tina,

You're solution works beautifully, final code below. I set the combo box
'List Rows' property to only 1 so even if the user does click the down arrow
they wont see other recording titles initially.

Thanks again.

Penny.

Option Compare Database
Option Explicit

Dim mintRecordingID As Integer

Private Sub Form_Delete(Cancel As Integer)

mintRecordingID = Me.RecordingID

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

If Status = acDeleteOK Then
CurrentDb.Execute "DELETE * FROM tblRecordings WHERE RecordingID=" &
mintRecordingID, dbFailOnError
End If

End Sub

 

[tina]

you're welcome

Tina,

You're solution works beautifully, final code below. I set the combo box
'List Rows' property to only 1 so even if the user does click the down arrow
they wont see other recording titles initially.

Thanks again.

Penny.

Option Compare Database
Option Explicit

Dim mintRecordingID As Integer

Private Sub Form_Delete(Cancel As Integer)

mintRecordingID = Me.RecordingID

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

If Status = acDeleteOK Then
CurrentDb.Execute "DELETE * FROM tblRecordings WHERE RecordingID=" &
mintRecordingID, dbFailOnError
End If

End Sub