Deletion event

[GordonR]

I want to detect when a record in a form is actually deleted i.e after the user has actually confirmed deletion. At this point, I want to be able to perform some additional processing - put back into stock the quanity of the item previously issued that is now being deleted.
However, the AfterDelConfirm event is always fired regardless of the response to the confirmation pop-up - how can I tell if yes was selected?
Thanks!

 

[Dugantrain]

I believe that the On_Delete event will fire after a record has truly been deleted; give it a try.

 

[GordonR]

Thanks for the reply; however the ondelete event is triggered before the confirmation request (as is the beforedelconfirm).
The only one that occurs after the confirmation has been requested is the afterdelconfirm (regardless of response). I would have expected this event to maybe only have triggered if the confirmation was in the affirmative but that is not the case. So how can you detect the response?

 

[GordonR]

Think this is driving me nuts!
I have attached a bit of code to the ondelete event in an attempt to replace the standard delete actions:

Private Sub Form_Delete(Cancel As Integer)

Dim Answer As VbMsgBoxResult
Dim SQL As String

Answer = MsgBox("Are you sure you want to delete this issue?", vbYesNo)

If Answer = vbNo Then
Cancel = True
Else
DoCmd.SetWarnings False
SQL = "DELETE FROM issue WHERE ID = " & ID
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
DoCmd.Requery
End If

End Sub

but I get some nasty 'Operation not supported in transactions' errors although the subform does display the record I have got rid of with #deleted values in all fields (which looks horrible!)

I had seen some postings related to using delete buttons but had wanted to just replace the standard process if possible and not have to take up space on the subform with a button (the code for which would need to check for the new record line I guess). Also wasnt sure if the form should not allow deletions.

Really would appreciate some help as I seem to be lurching from one issue to another!

 

[Rich]

Forms!MainFormName!SubFormName.Requery

 

[arvindn]

The answer is very simple

form_afterdelconfirm(status as integer)

If status=0 then
' Write the code u want to be executed
endif

If you need to update a table based on the key value of the record that was deleted use the form_delete event to store it in variables.

 

[GordonR]

Thank you very much for the replies.

It was the status that I was looking for all along! So, my code now has a public declaration of a variable that has its value set in the Form_Delete and then used in the Form_AfterDelConfirm as
If Status = 0 Then
Forms![Product Maintenance]![In Stock] = Forms![Product Maintenance]![In Stock] + QtyIssued
End If

My sanity is restored!