Subform Deletion Question

[RJ]

Acc 2003 / Sql Server 200

I have a datasheet subform and I want to allow record selection and deletion
of selected records. That part works fine. What I would like to do is to
prevent deletion of one particular record (based on record values) that may
or may not be in the record(s) selected record.

This being an ADP the deletion events fire in a different order than a
standard mdb. ADP = BeforeDelConfirm, AfterDelConfirm and finally the Delete.
However in the BeforeDelConfirm I have no way to review the selected
records. Is there a way to review the selected records prior to the Delete
event? Which in the case of ADP is too late to cancel the actual deletion?

The only thing that occurs to me is to review the records after the delete
and re-add my ‘required record’ if it has been deleted. That seems silly to
me however.

I hope this makes sense and thanks for any input!

RJ

 

[mazacz mirek]

Acc 2003 / Sql Server 200

I have a datasheet subform and I want to allow record selection and deletion
of selected records. That part works fine. What I would like to do is to
prevent deletion of one particular record (based on record values) that may
or may not be in the record(s) selected record.

This being an ADP the deletion events fire in a different order than a
standard mdb. ADP = BeforeDelConfirm, AfterDelConfirm and finally the Delete.
However in the BeforeDelConfirm I have no way to review the selected
records. Is there a way to review the selected records prior to the Delete
event? Which in the case of ADP is too late to cancel the actual deletion?

The only thing that occurs to me is to review the records after the delete
and re-add my 'required record' if it has been deleted. That seems silly to
me however.

I hope this makes sense and thanks for any input!

RJ

 

[RJ]

For all those who visit....

The simplist apprioach was to use a trigger that prevented (via a rollback)
an unwanted deletion. The other trigger option was to read from the deleted
table and add it back in after it was deleted. The deleted / inserted table
in Sql 2000 is in the transaction log which can slow down performance. In
Sql 2005 they moved these tables to tempdb.

 

[Greg Snidow]

How are you allowing your users to delete selected records? If you are using
a command button it would be easy to prohibit the deletion of certain
records. Here is a generic example:
--==========================================
Dim strSQL as String
strSQL = "DELETE SubFormTable " & _
"FROM SubFormTable T " & _
"WHERE T.PKfield = '" & forms!Subform.SubformIDField.value & "'

IF forms!SubForm.SubFormfield.value = whatever value is not allowed to be
deleted THEN
MsgBox "You can not delete this record", vbokcancel
Else DoCmd.RunSQL strSQL
End If
--===========================================

This would go behind the on click event of a command button on your main
form, and it is assuming you have some sort of ID field on the subform, and
of course you would have to mess with it, but you get the idea. I would not
recomend re-inserting deleted records from the deleted table, if for no other
reason than your users are thinking they are deleting something, without
seeing any message telling them they can not delete it. If they saw a
message they may actually learn not to do it again.

Greg Snidow