Unbound vs Bound Forms (1 Viewer)

[CJ_London]

There is no "after delete" event.

there is also the before and after delete data macros.

 

[gemma-the-husky]

I might start a new thread, but where I was coming from was that we upload orders to a RestAPI service, so when we modify an orderline, we have to upload the whole thing (the current order). modify is OK, because we get a form afterupdate event for the amended line. But when we select a row, and click delete there's no event, as far as I can see. You just have an order that previously had 5 lines, and now has only 4.

So I don't really need the order header form to know an orderline was deleted, but I do want the order lines subform to know, and I can't see how it can using any available event.

Hence I disabled the "allow deletes" option on the form, and forced the use of a button click, so that I can do something after the delete instruction.

 

[Pat Hartman]

But when we select a row, and click delete there's no event, as far as I can see. You just have an order that previously had 5 lines, and now has only 4.

Did you look at any of the events I mentioned? Every action has events to control it INCLUDING delete. But, the events are raised for the subform, not the mainform.

 

[Mike Krailo]

@gemma-the-husky I have a demo of updating tabs with record counts of sub forms on different tabs. This seems like a similar problem that came up on deletes and it was very frustrating. I ended up using the same solution you did by creating a delete button in the sub form itself and turned off allow deletions unless that delete button is pressed. Now I turned the record selectors back on and use the DEL key and somehow it's working properly. Not sure how it is working, but it is. See attached file.

@Pat Hartman , I tested those events and in order for those events to work, the Confirm Record Changes has to be turned on. To suppress the popup message for confirmation of deletion just set Response = acDeleteOK.

 

[Pat Hartman]

I would never in a million years consider turning off "Confirm record changes" so I never noticed that.

Adding a button to the detail item isn't a bad solution but Dave was looking for some event on the main form that got triggered by action on the subform and that is not available, nor would it ever be since it implies violating normal forms.

 

[Mike Krailo]

I would never in a million years consider turning off "Confirm record changes" so I never noticed that.

That's funny, I would never want to turn them on in favor of custom delete message or no message at all because in some cases I don't care about the warning. But maybe you know something I don't. What am I losing by turning them off other than that annoying default warning message? I clicked delete to do... guess what?

Now, if is something critical that would cause cascade deletes or some other important change, then I use a customized message that includes the record info to be deleted. It just looks better than the default one that you allow everyone to see.

On a side note, I had fun today learning more about how the delete event order works. I now understand that much better now. The reason I never learned it before was that nothing ever got deleted. So now I'm playing around with it.

 

[gemma-the-husky]

I would never in a million years consider turning off "Confirm record changes" so I never noticed that.

Adding a button to the detail item isn't a bad solution but Dave was looking for some event on the main form that got triggered by action on the subform and that is not available, nor would it ever be since it implies violating normal forms.

I was just looking for an event. I didn't expect one on the main form, but I couldn't find one on the subform either. I couldn't find any way of signalling that a record delete had just taken place, so do something. Once it had taken place, I need to re-process the entire order, As I say, the only way I could find was to treat the form as if it were unbound, and manage the process steps with a button click.

Maybe the AfterDelConfirm would have worked. I thought the after delete confirm was still before the delete took place, but I have just read it carefully, and it seems to fire after the delete has taken place. I thought it fired after you had confirmed you wanted the delete to happen but before the delete took place.

Form.AfterDelConfirm event (Access) | Microsoft Docs

Hmm. I am using a SQL Server back end, and this part of that article says the delete has not yet happened with SQL Server (in the AfterDelConfirm.)

"If you confirm the deletion, Access opens a transaction on SQL Server, issues the DELETE statement to delete the record or records, and fires the form's Delete event. If you choose No when prompted to confirm the deletion, Access does not open a transaction on SQL Server to delete the record and does not fire the form's Delete event."

 

[Pat Hartman]

There is also an AfterDelete event. But remember, the "after" events know NOTHING about the record that was just deleted. If that is important to you, you have to save the data you need in form level variables.

 

[gemma-the-husky]

@Pat Hartman

Sorry, there isn't an afterdelete event as far as I can see.

Form.Delete event (Access) | Microsoft Docs

This says in the first line
"Occurs when the user performs some action, such as pressing the Delete key, to delete a record, but before the record is actually deleted" (although the article seems to say something different)

I have an order record with 4 order lines. After I delete an order line, I now have an order with 3 order lines. Let's say I want a way to signal to the user "Order line deleted. There are now 3 order lines on this order". (What I really want is to update a RestAPI at this point because the number of order lines has just changed) I can't see an easy way to do that using any events that deal with the delete. Until the record HAS been deleted, there are still 4 order lines. The order line count drops to 3 only AFTER the delete is completed, but the seems to be no way to identify that point.

Maybe it's different because it's SQL Server, rather than a Jet/Ace back-end. I referred to that earlier in #67. MS say the afterdelconfirm event also occurs before the deletion, with a SQL Server database.

Anyway, this is where I see the similarity between using an unbound form and managing a deletion.

 

[MajP]

Maybe it's different because it's SQL Server, rather than a Jet/Ace back-end. I referred to that earlier in #67. MS say the afterdelconfirm event also occurs before the deletion, with a SQL Server database.

I do not think so. I think the issue is the same both ways. You are right there is not after delete, and I have no idea why not. Like you I wrestled with this and try all kinds of ways. Resorted to only allowing deletes from a form button. If you keep the confirm method then you can store the number of records before the delete confirm and the number of records after. Then you can raise a custom event if the number is different. This is counterintuitive because if the number is different then the delete was canceled. You would think in the before delete confirm the number of records would be the number before deleting, but it is the number you would have after deleting. I am guessing this is a transaction and if you cancel it rolls back.

 

[Pat Hartman]

I swear there was one there when I posted the last thread. The Delete event help entry explains the sequence of events. The AfterDelConfirm event is the one you want. It also looks like it is the Delete event that runs for each record but the confirm events run only once each.

The AfterDelConfirm event occurs after a record or records are actually deleted or after a deletion or deletions are canceled.

Form.Delete event (Access)

Office VBA reference topic

docs.microsoft.com