Problem Deleting Records Using Custom Delete Sequence (1 Viewer)

[shadow9449]

I've developed a custom delete sequence that I think many people could benefit from. The problem is that it's not working properly and I can't figure out why not.

The goal is that if a user deletes an invoice, the user is prompted for a reason for the deletion and then the cancelled invoice is logged with the Invoice Number, Client Name, Amount and Reason for deletion. This makes auditors (and accounting and management) happier rather than just see missing invoices and it's more user-friendly than just not allowing people to delete their mistakes.

Here's how I implemented it:

- In the form's Delete method, I put a few lines of code that opens a form in dialogue mode (that way it won't just delete until the user finishes entering the reason) with a text box where the user can enter a reason. There is an OK and Cancel button on this screen. I have a global variable called CANCELDELETE which is set to True if the user clicks cancel and False if the User enters a reason and clicks OK. When the user clicks ok, there is an SQL statement that INSERTS the information about the invoice into the Cancel Log

- The next line of code in the Delete method simply says Cancel = CANCELDELETE. That way the user had a chance to abort the deletion from the screen mentioned in the previous step.

Sounds pretty simple and useful but here's what happens:

- When you click Delete the invoice disappears. This is normal as it's placed into a buffer until the Delete method, Before_DelConfirm and After Del Confirm all fire.

- The dialogue box opens where you enter the reason.

- When you click ok, the invoice is gone.

- When you come back to the invoice, it's back! It's logged correctly.

I've played extensively with different tests including reading the response code from the After_del_Confirm method and I get a response that the deletion occurred properly.

The part that's most difficult is that this happens about 50% of the time and the other 50% of the time the invoice is indeed deleted.

So, my questions are:

- Does anyone know WHY an invoice would appear to delete and then come back?
- If not, can anyone recommend an alternate method of allowing a user to enter a deletion reason?

Thanks very much

SHADOW

 

[gemma-the-husky]

because probably what happens is

a) you delete the record - now what you actually see is a snapshot of the dbs - so your record is deleted from the snapshot.

b) you cancel the delete - so i guess your code REINSERTS the record back into the table (is there an autonumber Id - this might prove that a different record is being inserted)

c) so unless you REQUERY the data your snapshot will not refeelct this re-insertion

d) which is what happens when you close/reopen the form

---------
if this works, then fine, but personally i would not want to physically delete a record, and reinsert it - i would prefer to just set a deleted flag in the original invoice record

 

[shadow9449]

because probably what happens is

a) you delete the record - now what you actually see is a snapshot of the dbs - so your record is deleted from the snapshot.

b) you cancel the delete - so i guess your code REINSERTS the record back into the table (is there an autonumber Id - this might prove that a different record is being inserted)

c) so unless you REQUERY the data your snapshot will not refeelct this re-insertion

d) which is what happens when you close/reopen the form

---------
if this works, then fine, but personally i would not want to physically delete a record, and reinsert it - i would prefer to just set a deleted flag in the original invoice record

Why would the record be reinserted if Cancel is set to FALSE?

SHADOW

 

[gemma-the-husky]

Cancel = CANCELDELETE.

this may be the problem

to cancel a cancelable event you really need

Cancel = vbcancel

i am not sure offhand what numeric value vbcancel is, although you could find out. I just always use vbcancel, without worrying/bothering what the value actually is [edited - there you go just checked vbcancel is 2 - neither true nor false, in terms of a boolean!]
so if in your case canceldelete is NOT the same numeric value, then the delete will not actually be cancelled

eg your canceldelete is a boolean, not a numeric so maybe you should say the following. I really dont like the pratice of assuming numeric values for true or false.

if canceldelete then
  cancel = vbcancel
end if

 

[shadow9449]

this may be the problem

to cancel a cancelable event you really need

Cancel = vbcancel

i am not sure offhand what numeric value vbcancel is, although you could find out. I just always use vbcancel, without worrying/bothering what the value actually is [edited - there you go just checked vbcancel is 2 - neither true nor false, in terms of a boolean!]
so if in your case canceldelete is NOT the same numeric value, then the delete will not actually be cancelled

eg your canceldelete is a boolean, not a numeric so maybe you should say the following. I really dont like the pratice of assuming numeric values for true or false.

if canceldelete then
  cancel = vbcancel
end if

Wow...reading the Access documentation it seemed that Cancel was a Boolean value...I'll try this and see if it helps!

Thanks much

SHADOW

 

[gemma-the-husky]

out of interest

MsgBox (vbCancel)
MsgBox (vbOK)
MsgBox (vbYes)
MsgBox (vbNo)
MsgBox (vbYesNo)
MsgBox (vbOkCancel)

2,1,6,7,4,1

 

[shadow9449]

This didn't help either...I'm looking in the Access documentation and vbCancel is actually a value (2) indicating whether a MsgBox return value was OK or Cancel.

Looking at the Access documentation, the way they cancel deletes in their examples is Cancel = True

SHADOW.

 

[shadow9449]

If this helps any, I put a message box at the end of the code:

Msgbox CANCELDELETE and it says false, which means that the code is NOT cancelling the delete!

SHADOW

 

[gemma-the-husky]

actually im just looking again

Private Sub TextField_BeforeUpdate(Cancel As Integer)
End Sub

I cant see in help the correct way to use/set the cancel parameter

I have ALWAYS used cancel = vbcancel - the sub defines cancel as integer, so it clearly isnt necessarily the same as true/false, unless 0 is fasle and anything else is true maybe ....

 

[shadow9449]

actually im just looking again

Private Sub TextField_BeforeUpdate(Cancel As Integer)
End Sub

I cant see in help the correct way to use/set the cancel parameter

I have ALWAYS used cancel = vbcancel - the sub defines cancel as integer, so it clearly isnt necessarily the same as true/false, unless 0 is fasle and anything else is true maybe ....

As such, I tried

If CANCELDELETE = True then
  Cancel = True
  End if

That didn't help any...

 

[Banana]

Just to clarify something:

The "True" value is basically a NOT False. Therefore, any nonzero number will evaluate to True and only and exactly just zero will evaluate to False.

To demonstrate:

Dim i As Integer

i = 255

If i Then
   Debug.Print "True"
Else
  Debug.Print "False

If i = True Then
   Debug.Print "True"
Else
  Debug.Print "False"
End If

i = -1

If i Then
  Debug.Print "True"
Else
  Debug.Print "False"
End If

If i = True Then
  Debug.Print "True"
Else
  Debug.Print "False"
End If

Any implicit evaluation (e.g. If i Then) will be true, while equality evaluation will think True = -1 and 255 = -1 isn't true. Cancel = True or even Cancel = 255 or Canel = -65535 are all same in this context...


To the question at hand-

Here's alternative: Add a flag to your table in question.. call it "Deleted", a Yes/No. No need to show that flag. In Delete code, always cancel the event, set the flag to true, and requery the form and move to the next record relative to the deleted record.

Much simpler than mucking around with form's delete code, which I 've found to be iffy. When you are ready, you can execute a delete query to delete all records with Deleted flag set to true. Users never sees those "soft-deleted" because the form's recordsource should exclude all records where the flag is set.

Hope that helps...

 

[gemma-the-husky]

i've checked again, and I now think I am incorrect - the help says set cancel to TRUE to cancel the update, so yuor code should work!

However, with respect to your issue, it might be this

when you open your enquiry form to get the cancel/ok and, the reason - is this in modal/dialog form. If not, the form will open, BUT YOUR CODE in the confirmdelete will just continue to run, and it will use the CANCELDELETE setting of false - because you havent YET said you want to cancel the update

test this - just put a msgbox in your confirmdelete code AFTER the docmd.openform. but before the cancel = canceldelete - this will show you whats happening

 

[shadow9449]

To the question at hand-

Here's alternative: Add a flag to your table in question.. call it "Deleted", a Yes/No. No need to show that flag. In Delete code, always cancel the event, set the flag to true, and requery the form and move to the next record relative to the deleted record.

Much simpler than mucking around with form's delete code, which I 've found to be iffy. When you are ready, you can execute a delete query to delete all records with Deleted flag set to true. Users never sees those "soft-deleted" because the form's recordsource should exclude all records where the flag is set.

Hope that helps...

That certainly is worth a shot. I'm glad that someone else found the form's delete code to be iffy...

 

[Banana]

Yes, I remember having to walking on eggshells long ago when I worked the kinks out with that code, which was partially what drove me to look for other way, and ended up with Deleted flag. Wasn't my idea- I got it from someone else on this very board. (Bob Larson, maybe?)

 

[shadow9449]

when you open your enquiry form to get the cancel/ok and, the reason - is this in modal/dialog form. If not, the form will open, BUT YOUR CODE in the confirmdelete will just continue to run, and it will use the CANCELDELETE setting of false - because you havent YET said you want to cancel the update

test this - just put a msgbox in your confirmdelete code AFTER the docmd.openform. but before the cancel = canceldelete - this will show you whats happening

Yes, that's one of the places I checked.

Truth is that if the Cancel would have been continuing as FALSE, then the problem would have been that it would always be deleting (even when the user cancels) rather than SOMETIMES deleting and sometimes not when the user clicks OK

 

[shadow9449]

Yes, I remember having to walking on eggshells long ago when I worked the kinks out with that code, which was partially what drove me to look for other way, and ended up with Deleted flag. Wasn't my idea- I got it from someone else on this very board. (Bob Larson, maybe?)

I'm just thinking here...

If I do requery, I think what will happen is that it will go back to the first record in the recordset rather than the next record.

So, if the client has 6 invoices on file and I want it to pretend to delete invoice #4, (pretend until the user closes the form, for example, in which case it silently runs a delete query) then it will take the user to Invoice #1 of that client, not invoice #5 which will look really odd to the user unless there's a way around that.

 

[Banana]

You have to fetch the bookmark for the next record before you requery.

 

[shadow9449]

1)

You have to fetch the bookmark for the next record before you requery.

How do you do that?

2) I've set the recordset of the form to SELECT * FROM Invoice WHERE Invoices.Deleted = False. Requerying takes me to the first record but does not seem to refresh the recordset as the invoice that's marked deleted is still in there. I've tried both Me.Requery and Me.Form.Requery

3) I put in the On Close for the form Docmd.RunSQL " Delete * from Invoice WHERE Invoices.Deleted = True". This works about half the time. I'm thinking that perhaps the record set as deleted is not written to the database until AFTER the form is closed?

Thanks

SHADOW

 

[Banana]

This is off the top of my head and air code:

Dim v As Variant

With Me.RecordsetClone
   .MoveNext
   Set v = .BookMark
End With

Me.Deleted = True
Me.Dirty = False
Me.Requery
Me.Bookmark = v

2) Did you verify that the flag was actually set? (Note that I made sure the record was saved)

3) I normally don't use DoCmd.RunSQL, especially with all warnings it does. Better to use CurrentDb.Execute:

CurrentDb.Execute "MyQueryName", dbFailOnError

 

[shadow9449]

This is off the top of my head and air code:

Dim v As Variant

With Me.RecordsetClone
   .MoveNext
   Set v = .BookMark
End With

Me.Deleted = True
Me.Dirty = False
Me.Requery
Me.Bookmark = v

2) Did you verify that the flag was actually set? (Note that I made sure the record was saved)

3) I normally don't use DoCmd.RunSQL, especially with all warnings it does. Better to use CurrentDb.Execute:

CurrentDb.Execute "MyQueryName", dbFailOnError

I did check that the flag was set. I kept the deleted flag visible until I get this thing working!

What did you do in your code that makes sure that the record was saved. This must be where I'm missing something...