Intercept the Delete Key/Function on a Subform

[hbsclaims]

I have a subform based on a query. I do not want the user to delete records
due to the intricate level of relationships in my database, however I do want
to hide the records that the user has chosen to delete. In the onDelete
event I intercept the call and set the property of a field called hide to
TRUE. Then I Requery the form to show changes? Except that the underlying
table has not been updated yet so the requery doesn't show changes because
they have not happened yet. I have tried, refresh, acSaveRecord. I added a
watch to the code and tracked the value of the field in the underlying table
and it did not update in the OnDelete Event. How can I either force the
update to the table or where do I put the Requery to show changes?

Thanks
DLM

 

[Allen Browne]

So you want to cancel the deletion, and instead set a flag field to mark the
record as inactive, and then requery the form so it shows only the active
records.

The simplest solution might be to create your own Delete button to execute
the update query and requery the form. Depending on your interface, this
might also mean using a custom menu, toobar, and shortcut menu (for
right-click) as well.

The difficulties with using the built-in events are:
a) It is possible to select multiple records at once for deletion.
b) The timing of the events.

I've no idea if this would work, but you you could try something like this:
1. Declare a module-level string to use as the WHERE clause for identifying
flagged records:
Private mstrDeleteWhere As String

2. In the Delete event, add the primary key value to the string:
mstrDeleteWhere = mstrDeleteWhere & Me.ID & ","

3. In the BeforeDelConfirm event, cancel the deletion and suppress the
confirmation message.

4. In the AfterDelConfirm event, build an Update query string to set the
flag for the chosen records. This kind of thing:
lngLen = Len(mstrDeleteWhere) - 1 'Without trailing comma
If lngLen > 0 Then
strSql = "UPDATE Table1 SET Inactive = True WHERE ID IN (" &
Left$(mstrDeleteWhere, lngLen) & ");"
dbEngine(0)(0).Execute strSql, dbFailOnError
End If

mstrDeleteWhere = vbNullString

As I say, I'm not sure of the timing of all that. For example, Access 2002
XP2 has problems with AfterDelConfirm anyway (complaining about 'no curent
record.')

 

[hbsclaims]

I tried your code and it performed the same as what I had previously. The
issue seems to be when the record is actually written. I think that Access
must prevent writing to the record from within the OnDelete event, because
after I "delete" the record with my code, then click into another subform,
then click back the subform shows the records correctly. My query for the
subform links 3 tables so the delete function does not work anyway due to
linked records and hence I cannot use the AfterDelConfirm Event. I also
don't think that it fires if Cancel=True. Now for the question. Is there an
event that fires after the OnDelete Event that I can use to insert my code to
update the record and requery the form?
Thanks
DLM

 

[Allen Browne]

Did you read the previous reply carefully? I explained that 2 other events
fire after the form's Delete event, and nowhere suggested cancelling the
Delete event.

Certainly, Access does start a transaction when the Delete event fires. I'm
unclear whether the transaction has been committed/rolled back in
AfterDelConfirm, so my code may fail there also.

I'm also unclear whether the approach I suggested will work in all versions
of Access, for all levels for form/subform. For example, Access 2002 SP2
gets confused in AfterDelConfirm, and Access 2007 (including SP1) gets
confused when deleting from split forms and sub-subforms in datasheet view
as explained in the last 2 items of the 'bugs' table here
http://allenbrowne.com/Access2007.html#Bugs

Hence, the suggestion that rolling your own delete button might be the
better solution.