Deletion Problem

[Guest]

I am trying to set up my form so that when i delete a record in one subform,
a similar record is deleted from another subform. Each subform uses a
different junction table and therefore i think i need to "search" for the
record prior to deletion. I get an error when i try to run it, saying that
the record cannot be deleted from those tables. Here is my code, i hope
someone can point me in the right direction:

Private Sub Form_Delete(Cancel As Integer)
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID FROM qryWWindowsXREFModels WHERE
qryWWindowsXREFModels.Model_ID = " & Me.Model_ID & " AND
qryWWindowsXREFModels.SupplyPart_ID = " & Me.SupplyPart_ID & ";"
'Set rst = db.OpenRecordSet(SQL, dbOpenDynaset)
Set rst = db.OpenRecordset(SQL)
If rst.RecordCount = 1 Then
SQL = "DELETE FROM (SELECT Model_ID, SupplyPart_ID FROM
qryWWindowsXREFModels WHERE qryWWindowsXREFModels.Model_ID = " & Me.Model_ID
& " AND qryWWindowsXREFModels.SupplyPart_ID = " & Me.SupplyPart_ID & ");"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

 

[Allen Browne]

There are several issues here.

Firstly, the records are not really deleted during the Delete event. Access
has them in a transaction that can be cancelled if the user does not approve
the deletion. That's probably the reason you are unable to delete the other
records (assuming they are related to the ones in the transaction.)

Secondly, the user may select several records (in a continuous form or
datasheet) and delete them at once. The Delete event fires for each one, but
the record is not deleted yet and may not be (e.g. if the user cancels it in
the form's BeforeDelConfirm event.) It is therefore not safe to delete other
records in Form_Delete.

You can use the form's AfterDelConfirm event, testing the Status argument to
see if the delete really occurred. However, details of the records that were
deleted are not available in this event, so you will need to write them to a
temp table or array in Form_Delete, and then loop through them to delete the
other records for each one.

If the other records are actually related to the ones being deleted, is
there any chance of setting up a relation with cascading deletes? This would
take care of the whole process, no code needed.