Requery a sub form

[MarionD]

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!

 

[RuralGuy]

Try saving the modified record first.
DoCmd.RunCommand acCmdSaveRecord

 

[MarionD]

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

 

[MarkK]

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.

 

[MarionD]

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

 

[MarionD]

Works very well !

Thank you soooo much!