In a sub form I don't want any records to be physically deleted to maintain data integrity (foreign key) therefore I have a field "deleted" (boolean) in the subfrom.

In the "on delete" of the subform, I set the "deleted" field to yes, and docmd cancel Event, to stop the record being deleted. This works fine but, I cannot seem to requery the sub form in this same procedure. (The recordsource of the sub form is set to show only records where "deleted"=No.)
Error message 3246 - Operation not supported in Transaction.

I have tried all the versions of
me.parent.formname.subformname.form.requery
me.requery ( I am in the sub form so me.requery should work??)
forms!Mainform!subform.form.requery

Any Ideas how I can get this record marked "deleted" to dissappear without having to close and open the form again?

Any help most appreciated!

Try saving the modified record first.
DoCmd.RunCommand acCmdSaveRecord

Hi there,

I do save the record first -I've tried now putting the requery in the "on Current Event" of the sub form - it works as soon as I leave the record (by enter or tab), but not when I docmd.gotorecord, nextrec in the procedure in the "on delete".

It looks a bit untidy when I have to physically leave the record.

Thanks

Here's a little dodge I've used to solve your problem. Start the timer before cancelling your delete event, then do your work in the Form_Timer() event handler.
Private Sub Form_Delete(Cancel As Integer)
Me.TimerInterval = 5
Cancel = True
End Sub

Private Sub Form_Timer()
Me.TimerInterval = 0
Me.Deleted = True
Me.Requery
End Sub

An adverse side effect of this approach is that it only succeeds on one record at a time. If your user highlights multiple records in a datasheet, say, and runs a delete, only the first record is modified and filtered out. After that the event is cancelled without error, but it is unexpected behaviour.
Where I've used this I don't want the user to delete multiple records anyway so it's not a problem.

Thanks a lot, will try in the morning! Have to leave now.

Works very well !

Thank you soooo much!