OnDelete Event of a subform

[Dudley]

I have a problem I'm failing to figure out and I wonder if anyone cares to help me sort it out.

I'm trying to delete records from a subform in datasheet view. It works fine when I press the DEL key - the selected record (with a FK) is deleted after I respond affirmatively to the MSGBOX.

The problem is that I have a small redundancy built into my tables such that I actually need to delete records in two tables, and I can't get the records in both tables to be removed. The relationship between the tables does not enforce referential integrity. There is a PK/FK relationship between the tables - the table referenced by the subform holds the FK. Neither of the data sources for the subform nor the parent form reference the table with the PK. I *think* the table with the PK is essentially independent of the one with the FK. If I delete the record in the PK table, the one in the other table remains. I just can't get it to work in code and I'm hoping for a solution where the user just selects the record in the subform and hits DEL.

If I try a :

response 'do you want to delete the record?'...
if response <> vbyes then 
   cancel=true
else
     do things, including docmd.runSQL "DELETE * FROM tblWithPK WHERE ..."
end if

in the OnDelete event of the subform, the record in the table with the FK is deleted, but not the other (the docmd.runSQL command). I don't think it's the syntax, when I test the SQL statement in a DELETE QUERY, it works fine. Sometimes I get an error message about two users trying to edit the record at the same time. How do I prevent this? I don't know where the second user is coming in. Can I regulate the sequence of the deletes somehow? At what point in the OnDelete event is the deletion actually performed?

Can anyone help me understand why this won't work? I've been up all night trying to get this to go, so I may be simply unable to comprehend the Help files about the OnDelete event and Before/AfterDelConfirm. This is my first foray into OnDelete.

Thanks for any help anyone can give me!

 

[ted.martin]

The way that I do it is to use the OnkeyPress event of Ctrl + D and run two Delete queries realting to the two tables.

This is my code which would need to be adapted for your solution:
(note: is there is no field in the seconds SQL statement then no error is created - LineNo is the common field in both tables.)


Private Sub PartNo_KeyPress(KeyAscii As Integer)

If KeyAscii = 4 Then ' Ctrl + D
Beep
If MsgBox("Do you wish to DELETE this Part No. line?", vbQuestion + vbYesNo, "Delete Part No. Line") = vbYes Then

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE [Table1].*, [Table1].[FieldName] FROM [Table1] " & _
"WHERE ((([LinesNo]) = " & LineNo.Value & "))"

DoCmd.RunSQL "DELETE [Table2].*, [Table2].[LineNo] FROM [Table2] WHERE ((([LineNo]) = " & LineNo.Value & "))"

DoCmd.SetWarnings True

Me.Requery

End If

End If

 

[MarkK]

A few observations:
- If you use the Execute method of a DAO.Database object then there are never warnings. Also, you can set that method to raise an error if the SQL fails for any reason using the dbFailOnError option.
- It makes no difference in a DELETE query to specify fields because a delete targets the whole record.
So this ...

If MsgBox("Do you wish to DELETE this Part No. line?", vbQuestion + vbYesNo, "Delete Part No. Line") = vbYes Then
  With DoCmd
    .SetWarnings False
    .RunSQL "DELETE [Table1].*, [Table1].[FieldName] FROM [Table1] WHERE ((([LinesNo]) = " & LineNo.Value & "))"
    .RunSQL "DELETE [Table2].*, [Table2].[LineNo] FROM [Table2] WHERE ((([LineNo]) = " & LineNo.Value & "))"
    .SetWarnings True
  End With
  Me.Requery
End If

... refactors to this ...

If MsgBox("Delete?", vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
  With CurrentDB
    .Execute "DELETE FROM Table1 WHERE LineNo = " & LineNo.Value, dbFailOnError
    .Execute "DELETE FROM Table2 WHERE LineNo = " & LineNo.Value, dbFailOnError
  End With
  Me.Requery
End If

 

[Dudley]

Thank you Thank you Thank you Thank you so very much for your generous responses. Between them and further searching on the form, I came up with the following:

Private Sub Form_Delete(Cancel As Integer)
    If MsgBox ("Do you want to delete...) = vbYes Then
        With CurrentDb
            'Delete the records in the source tables
            Select Case Me.lngEventType   
        '        Case 1 
                    .Execute "DELETE FROM ...", dbFailOnError
                Case 3, 4  ...
            End Select
'           .Execute "DELETE FROM tblData_Events_Students WHERE lngClientsEventsID = " & Me.lngClientsEventsID, dbFailOnError
         End With
    End If

What I learned is that the final commented out deletion - its the FK one and the one contained in the recordset of this form - and there's the rub: the reason I was getting a message about two processes trying to delete the same record was because of this line of code. Commenting it out removed the process conflict and everything works fabulously. I only needed to be adding code to remove the other, PK, records, the Delete Event was handling the FK record in the first place.

Thanks so much for helping me get this sorted out!!!

 

[MarkK]

Congrats:
If what you posted is your final code however, I'd still refactor as follows ...

Private Sub Form_Delete(Cancel As Integer)
  If MsgBox ("Do you want to delete...) = vbYes Then
    If me.lngEventType = 1 then
      CurrentDB.Execute "DELETE FROM ...", dbFailOnError
    End If
  Else
    Cancel = true
  End If
End Sub

Cheers,

 

[Dudley]

Okay, will reformat the code for the Else|Cancel=True part for sure. Did you see a problem with the Select Case structure? I need a different DELETE statement for 6 different tables.

 

[dfenton]

Because of various issues with the interrelationships between the delete events, I tend to never rely on the default Access interface for deleting records, except when I don't need to do anything special.

In the case you're in, I'd turn off the subform's AllowDeletes property, and create a DELETE CURRENT RECORD command button. That then allows you to completely control the record deletion in code, something like this:

  If VbYes = MsgBox("Are you sure you want to delete this record?", _
       vbYesNo, "Delete Record?") Then
     Me.AllowDeletions = True
     DoCmd.RunCommand acCmdDeleteRecord
     Me.AllowDeletions = False
     CurrentDB.Execute "DELETE FROM Table2 WHERE LineNo = " & LineNo.Value, dbFailOnError
  End If

Now, you'd obviously need to wrap that in an error handler, and you might want to turn off screen painting during the process, but basically that gives you full control over everything.

And I find it's much, much simpler and controllable to do this than to try to rely on the Access UI in situations like this where you really do need to do something more than just the default delete operation.