Problem Deleting Records Using Custom Delete Sequence

[shadow9449]

After some experimentation, I've concluded the following:

All in all, I'd say "screw it" and disallow deletions by setting AllowDeletions to false and provide your custom deletion sequence via a button/custom toolbar, where there are no buffers involved that we have no control over and you can requery whenever you need and setting the focus on the next record without fighting with Access.

Wow...thanks for all the research.

My toolbars already have a delete button that is used to delete any record from any form. I wanted the invoice screen to have a different behaviour for the purpose of logging the delete rather than adding more buttons. It sounds like this is not possible...

 

[shadow9449]

AFter saying it couldn't be done, I had an idea.

Still too much juryrigging for my taste, and I'd still prefer the custom button replacing the Access' deletion sequence, but at least this works in case where we want to keep Access's native deletion sequence...

I think that for now, I'll simply remove the user's ability to log a reason for deletion. If it's a toss-up between that neat functionality and the question of whether Access will inexplicably replace a deleted record, I'd opt to forgo the former.

I can still have it log the invoice number, client and purchase amount for audit purposes. If people want, I can even have it record the user's login name or computer name for the sake of accountability.

Thanks a lot, Banana...this isn't the first time you've been a great help!

SHADOW

 

[Banana]

Just to toss this out- If you want to provide button, you can use a custom button and execute your custom code instead and have it look like the built-in deletion...

 

[shadow9449]

... If you want to provide button, you can use a custom button and execute your custom code instead and have it look like the built-in deletion...

Do you mean having a button on the toolbar or on the form?

 

[gemma-the-husky]

On a legal type thing - in UK at any rate

you shouldnt EVER be deleting invoices legally - once youve issued an invoice, the cutomer has it, etc

so if its wrong, just issue a credit, and reinvoice.

----------
I think in your case, if you still want to "delete" invoices, I would just set a "deleted" flag (and record a reason in the same table probably, rather than in another table), and then exclude any deleted invoices from reports etc.

 

[Banana]

Shadow, yes. I believe it can be made to be a menu as well, in which case would require you to hide the Access's default menu.

 

[shadow9449]

On a legal type thing - in UK at any rate

you shouldnt EVER be deleting invoices legally - once youve issued an invoice, the cutomer has it, etc
.

You're right if the invoice has been issued. Sometimes people create invoices to train new staff, or they issue an invoice and then realize that it's the wrong Mr. Brown, or they enter information and then realize that they've botched up so badly that they'd rather wipe it and start again and so on. I've discussed this with a large number of accountants and bookkeepers and they almost all agree that this approach is valid.

----------
I think in your case, if you still want to "delete" invoices, I would just set a "deleted" flag (and record a reason in the same table probably, rather than in another table), and then exclude any deleted invoices from reports etc.

That would require rewriting almost all of the accounting features (receivables, sales reports and so on). As I said to Banana, I think I'll just suspend the ability to store the reason.

I think it's very sad that Access doesn't work as it claims to the degree that it's not possible to create this functionality. Microsoft is more focused on more eye-candy and "security" nightmares than on making Access work as it should.

SHADOW

 

[Banana]

Well, I wouldn't go that far. It's quite more likely that Access team envisioned Delete events as a mean to provide us to control whether a record is to be deleted or not (in very similar sense to how we use BeforeUpdate event to validate a record before it's saved) and didn't predict that we may want to soft-delete.

I think that if you try to requery or alter values in a BeforeUpdate event, you will run in the same problems.

I do think they could have had at least given us AfterDelete (e.g. after the buffer is cleared & closed) event, which would be the logical event to use in very similar vein to how we use AfterUpdate to alter some of values after the validation has passed in BeforeUpdate event, but again they didn't envision us needing it because by then, the record should be *gone*, so why need to act on that?

 

[shadow9449]

Well, I wouldn't go that far. It's quite more likely that Access team envisioned Delete events as a mean to provide us to control whether a record is to be deleted or not (in very similar sense to how we use BeforeUpdate event to validate a record before it's saved) and didn't predict that we may want to soft-delete.

I think that if you try to requery or alter values in a BeforeUpdate event, you will run in the same problems.

I do think they could have had at least given us AfterDelete event, which would be the logical event to use in very similar vein to how we use AfterUpdate to alter some of values after the validation has passed in BeforeUpdate event, but again they didn't envision us needing it because by then, the record should be *gone*, so why need to act on that?

Your comment is very valid for implementing the logging using the means that you suggested yesterday.

HOWEVER there should be nothing wrong using my original code to delete and log. There should be nothing about opening a form in dialogue mode and inserting a row in a table and then coming back to the delete event that SOMETIMES prevents the record from being deleted. I put flags in the AfterDelConfirm to see if it was going through the sequence and everything was firing correctly except the record came back when the form is reopened.

In fact, you said that the impetus for devising the method you recommended yesterday was BECAUSE the existing delete functionality as documented is not reliable enough to implement a custom sequence.

Dig?

SHADOW

 

[Banana]

I certainly understand, and I do confess that I'm a bit baffled to why AfterDelConfirm isn't really the same as AfterUpdate.. it's kind like AfterBeforeUpdateButStillBeforeUpdate event, which still has the same limitations as the BeforeUpdate events.

I did say earlier that it was iffy based on misunderstanding of AFterDelConfirm- I had assumed it would be the same thing as AfterUpdate but now know it's not really the same thing.

There is nothing wrong with your code, per se; it's just a lack of event (e.g. after the deletion... more specifically after the buffer has cleared) that's the problem and shame on Microsoft for that.

As for the 'soft-deleting' being the impetus for iffy delete handling, I don't think that was the case- I did see it as recommended but I couldn't find the original code or the original post suggesting it and for all I know maybe it doesn't even use the Delete events at all.

 

[shadow9449]

As for the 'soft-deleting' being the impetus for iffy delete handling, I don't think that was the case- I did see it as recommended but I couldn't find the original code or the original post suggesting it and for all I know maybe it doesn't even use the Delete events at all.

Ah, ok. If so, maybe if someone comes across the code, they can point me in the right direction?

In the meantime, I have no choice but to suspend it.

I would like to ask the following:

Usually it's considered inconsiderate to post a question on one forum and then post elsewhere because that causes people to go through the efforts in trying to help you while you've already had the problem solved. I think that the brains of AWF have pretty much come to a stand still as far as my original forum and I'd like to post this elsewhere if that is the case. Does anyone reading this thread object?

SHADOW

 

[Banana]

No, do feel free to go do so. A good courtesy would be to share the answers on the all forums, though.

 

[shadow9449]

No, do feel free to go do so. A good courtesy would be to share the answers on the all forums, though.

You bet I will.

I've sometimes asked a question here, not received an answer, then figured out a solution years later and then posted it. I hope I've helped someone that way.

SHADOW

 

[gemma-the-husky]

Shadow

I dont think thats a fair comment to make at all

1. You certainly can create the functionality you require

As i see it, you are trying to decide whether you wish to delete an item, or not - if you do, then you wish to store that information in another table, and then delete the item

so, it may be that using the particular event you are trying to use is not appropriate - either it is not possible with the event, or it is possible but you need to find a different mechanism. If it is not possible, then you need to find another way. Access is not a sequential language, it is event driven, and it is therefore hard to know exactly what sequence of events is needed to achieve certain things, without trying them.

2. eg - You say that your users are creating invoices to train staff. I would not like to do that at all - I would rather have a test system avaialble to demonstrate stuff like that. Its all very well deleting the invoice, but does your invoicing process update any other records, such as inventory etc. Similarly with deleting invoices because they were entered incorrectly. This is hardly the fault of access, and I presume that is where your original requirement came from.

-----------------
having discussed all of the above, i thought i would try to do what you wanted with the database you posted - so I added an invoicesdeleted table, and code to insert the deleted invoices, and it DID work exactly how you wanted it to, with just code in the form_delete event - nowhere else at all

ie - if you delete the invoice and enter a reason, it stores the deletion, and deletes the invoice. if you cancel the deletion nothing happens

so i am back to thinking that it is your code that is at fault, and i suspect the fault is in the code that adds the deletion to the deletion log, which you didnt post.

so can you post ALL the code in your form_delete event, so we can see what you have there.

 

[shadow9449]

You say that your users are creating invoices to train staff. I would not like to do that at all - I would rather have a test system avaialble to demonstrate stuff like that.
.

I agree. But they don't always ask my advice or permission

Its all very well deleting the invoice, but does your invoicing process update any other records, such as inventory etc. Similarly with deleting invoices because they were entered incorrectly. This is hardly the fault of access, and I presume that is where your original requirement came from.
.

The area that I fault Access with is not that the users entered something wrong. It's that there is something inconsistent with the deletion sequence that prevents the method I've implemented from working correctly all the time. The fact that it reports that a record has been deleted (using the Response code in the After Delete) and the record coming back SOMETIMES does indicate a glitch in there.

-----------------

having gone through all of the above, i thought i would try it with the database you posted - I added an invoicesdeleted table, and code to insert the deleted invoices, and it DID work exactly how you wanted it to, with just code in the form_delete event - nowhere else at all

ie - if you delete the invoice and enter a reason, it stores the deletion, and deletes the invoice. if you cancel the deletion nothing happens

so i am back to thinking that it is your code that is at fault, and i suspect the fault is in the code that adds the deletion to the deletion log, which you didnt post.

so can you post ALL the code in your form_delete event, so we can see what you have there.

Sure, I'll try that. The problem I'm having is that I tested the mockup database that I posted here and it DIDN'T work and then you did the same and it DID work. That's what's frazzling about this.

How did you delete invoices? Did you use the Delete button in the toolbar or did you click the record selector? I tried both and they both failed.

SHADOW

 

[shadow9449]

Here's an idea suggested in Utter Access:

Have the popup handle the delete using an SQL command.

So, the Delete event will be set to cancel and then open the popup. I'll open the popup NOT in dialogue mode so the delete will finish doing it's stuff. The popup can then delete and requery and advance to the next record as Banana was suggesting at it should look seemless to the user.

Anyone find any objection? It just might work...

 

[Banana]

Well, never having had done that specific route, it never hurts to experiment.

That said- I'd not be surprised if mysterious "write conflict" or "lock conflict" error starts to crop up. Maybe it won't be a problem because the user should have be on another row, but I've seen too many cases of people trying to manage recordset via SQL upon the same table/query that's bound to the form and getting that errors.

 

[gemma-the-husky]

what i did was press "delete" button on a given invoice


i added a table "invoicedelete", with similar fields to invoice

the code i had is as below, and it works fine

Private Sub Form_Delete(Cancel As Integer)
Dim sqlstrg As String
  DoCmd.OpenForm "Delete Invoice", , , , , acDialog
  Cancel = CANCELDELETE

    If Cancel Then
        MsgBox ("Cancelling delete")
    Else
        sqlstrg = "insert into invoicedelete select " & _
            "invoice , item, qty, price, client, reason " & _
            "values " & ID & " , " & Chr(34) & Item & Chr(34) & " , " & _
            Qty & " , " & Price & " , " & ClientID & " , " & Chr(34) & CANCELREASON & Chr(34)
        
        MsgBox ("Actioning Delete" & vbCrLf & sqlstrg)
        On Error GoTo fail
        CurrentDb.Execute sqlstrg, dbfailonerror
    End If

exithere:
    Exit Sub

fail:
    MsgBox ("Error saving delete reason " & vbCrLf & _
        "Error: " & Err & "  Desc: " & Err.Description)
    Cancel = True
    Resume exithere

End Sub

 

[shadow9449]

what i did was press "delete" button on a given invoice


i added a table "invoicedelete", with similar fields to invoice

the code i had is as below, and it works fine

Private Sub Form_Delete(Cancel As Integer)
Dim sqlstrg As String
  DoCmd.OpenForm "Delete Invoice", , , , , acDialog
  Cancel = CANCELDELETE

    If Cancel Then
        MsgBox ("Cancelling delete")
    Else
        sqlstrg = "insert into invoicedelete select " & _
            "invoice , item, qty, price, client, reason " & _
            "values " & ID & " , " & Chr(34) & Item & Chr(34) & " , " & _
            Qty & " , " & Price & " , " & ClientID & " , " & Chr(34) & CANCELREASON & Chr(34)
        
        MsgBox ("Actioning Delete" & vbCrLf & sqlstrg)
        On Error GoTo fail
        CurrentDb.Execute sqlstrg, dbfailonerror
    End If

exithere:
    Exit Sub

fail:
    MsgBox ("Error saving delete reason " & vbCrLf & _
        "Error: " & Err & "  Desc: " & Err.Description)
    Cancel = True
    Resume exithere

End Sub

Apparently, your CANCELREASON is global?

My approach was to have the SQL execution done by the Delete Form. Do you see any reason that would be any different or less likely to work?

 

[shadow9449]

That said- I'd not be surprised if mysterious "write conflict" or "lock conflict" error starts to crop up. Maybe it won't be a problem because the user should have be on another row, but I've seen too many cases of people trying to manage recordset via SQL upon the same table/query that's bound to the form and getting that errors.

I'm hoping the user won't be on another row. That way we can draw the invoice number into the deletion screen. It's also why I'm going to open the form and then cancel the delete. By the time the user enters anything into the popup, the delete sequence should be done with and the record SHOULD be unlocked.

If the theory fails, then I'm stuck