After DELETE, how to refresh (or requery)

[Cameo]

When a user DELETE's a record, how can I refresh or requery the form AFTER the delete's been done in the physical table? I am not performing a DELETE Confirmation, therefore I cannot use AFTER DEL CONFIRM or BEFORE DEL CONFIRM events. I've tried DIRTY-ing a control in the ON DELETE event, but the ON DIRTY event doesn't fire off (I've read that the DIRTY-ing has to be done by the user, not code.)
All help is greatly appreciated. Thanks.

 

[SOS]

How is the user deleting the record? If via a button then you can just add a Me.Requery after the code that deletes the record.

 

[Cameo]

One way is the user clicks on the left-hand record indicator arrow, and hits the DELETE keyboard key.

 

[gemma-the-husky]

When a user DELETE's a record, how can I refresh or requery the form AFTER the delete's been done in the physical table? I am not performing a DELETE Confirmation, therefore I cannot use AFTER DEL CONFIRM or BEFORE DEL CONFIRM events. I've tried DIRTY-ing a control in the ON DELETE event, but the ON DIRTY event doesn't fire off (I've read that the DIRTY-ing has to be done by the user, not code.)
All help is greatly appreciated. Thanks.

the before del confirm and after delconfirm dont happen

but there is also an ondelete event - that must happen.

all you need is to have me.requery. IN the ondelete event.

------------
[edited - i guessed the event order here, and got it wrong]

 

[Cameo]

The ON DELETE event fires off BEFORE the record is deleted from the physical table. I need to requery the table after it's been updated.

 

[gemma-the-husky]

i've just checked what happens, instead of guessing/trying to remember

when you delete an item, by code or by clicking delete, the recordset is automatically refreshed and the record just disappears. - you dont NEED to do anything.

Are you confusing this with a situation where you are looking at records, and SOMEONE ELSE (or another procedure) deletes one - in which case your records DO show "deleted" I think - but this is different.

 

[Cameo]

Yes, the record disappears from the form but not the table until after it's ended the ON DELETE event. You can debug the ON DELETE event and you'll see that the table record stays until you've left the ON DELETE event.
I have other form controls I need to requery after the physical record's been deleted.

 

[gemma-the-husky]

can you not use the current event?

if necessary, just set a flag in the delete event, to show there WAS a deletion

in the current event test this flag, and if necessary, requery the fields and then clear the flag

 

[Cameo]

Thanks, gemma. That was my last resort. I know even though it will be hitting the Current Event A LOT, it will be fast. But, so inelegant.
I was hoping someone knew a "secret" event or something. Thanks. That's the way I'm going.

 

[Cameo]

Anyone watching this thread should be learning a lot about DELETEing a form record and how it's treated in the background. Current Event doesn't help. Record still not physically deleted til after the Current Event. (So the global I set in On Delete to be utilized by Current Event didn't help.) So I tried changing a row field in the On Delete event to be picked up by the After Event. Guess what? Access won't let you do that; says "Operation not supported in transactions". Anyone terribly interested can read the VBA help for "Delete Event".
So, I'm still looking for an answer.

 

[vbaInet]

Are we talking subform level or form?

 

[Cameo]

It's on a subform; I'm wanting to update controls in the main form. But, from what I've read, this is not the problem.

 

[SOS]

Yeah, I'm confused on this as well as I've never had a problem with a requery of anything when deleting something. I'm wondering why you seem to have a problem as it doesn't make sense in the normal Access world of things. Perhaps a bit more information would be in order as to where things are, what you are trying to requery and why, and perhaps a screenshot or two to help us understand your flow.

 

[SOS]

A subform and a mainform have nothing to do with each other when it comes to updating. When you move to a subform the mainform is updated (if anything changes) and when you move from a subform to the main form the subform data is updated. So you have to have things in the right events on the right form to work.

 

[vbaInet]

Set focus on your main form, maybe in one of the controls, requery your subform (i.e. i don't mean Me.Requery, the full reference to the subform), then set focus back to your subform.

 

[Cameo]

I have a mainform that lists a count of items. The subform lists the items. If a user deletes an item, the mainform's count should decrement by requerying the underlying data table. This is a simplified description of what's needed.

 

[SOS]

So the subform's On Current event SHOULD be able to requery the Main Form

Me.Parent.Requery

and work.

 

[SOS]

But if it is calculations then a requery isn't necessarily the answer. You might just need:

Me.Parent.Recalc

 

[Cameo]

Can't use On Current as the record is not physically deleted til after the On Delete, On Current, and a few more events that fire off when the user deletes a record. Try it in debug - you'll see. Also, the Help for "Delete Event" states this.
I REALLY appreciate everything. I'm in the US, it's Friday, 5 PM, and we have a 3 day weekend, and I'm going home now. Just too much fun! I'll get back to it on Tuesday. (Although I'll be thinking about it all weekend.)

 

[SOS]

Can't use On Current as the record is not physically deleted til after the On Delete, On Current, and a few more events that fire off when the user deletes a record. Try it in debug - you'll see. Also, the Help for "Delete Event" states this.
I REALLY appreciate everything. I'm in the US, it's Friday, 5 PM, and we have a 3 day weekend, and I'm going home now. Just too much fun! I'll get back to it on Tuesday. (Although I'll be thinking about it all weekend.)

Umm, yes you CAN use the On Current but it has to be the Subform's ON Current. And I think you are off there about when it is really deleted and when it is DISPLAYED as deleted in the table. I've done this for 15 years and I've never had an issue like you are describing. Post your database if you want proof.