Problem Deleting Records Using Custom Delete Sequence
- Thread starter shadow9449
- Start date
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
It gives an error on Set v = .Bookmark. The error is "Type Mismatch"
- Local time
- Today, 01:59
- Joined
- Sep 12, 2006
- Messages
- 16,030
shadow
i've reread your posting more carefully now - you were opening the reposnse form modally etc
so another thing that may affect the deletion is some relational integrity thing
ie deleting the invoice fails because it would compromise some relational integrity constraint - although it seems unliely that an invoice would have this effect.
a similar possibilty is that the log insert fails, and you havent handled this error - which causes the sub to fail in some unspecified way, producing the effect you describe
i've reread your posting more carefully now - you were opening the reposnse form modally etc
so another thing that may affect the deletion is some relational integrity thing
ie deleting the invoice fails because it would compromise some relational integrity constraint - although it seems unliely that an invoice would have this effect.
a similar possibilty is that the log insert fails, and you havent handled this error - which causes the sub to fail in some unspecified way, producing the effect you describe
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
Good question. No, there are no contraints. I posted a mockup database if you want to test it. I just slapped something together in a few minutes that would show the problem
Thank again
SHADOW
- Local time
- Today, 01:59
- Joined
- Sep 12, 2006
- Messages
- 16,030
shadow - where is the attachment?
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
In the other thread. But I'll post it here for convenience 
- Local time
- Today, 01:59
- Joined
- Sep 12, 2006
- Messages
- 16,030
worked ok for me, just as you intended
incidentally, I am still getting the "you are about to delete confirmation message" - do you get this, or have you suppressed it.
did you see the other note on my last post
maybe the log insert fails, and you arent handling that bit correctly.
code for that wasnt in the sample.
incidentally, I am still getting the "you are about to delete confirmation message" - do you get this, or have you suppressed it.
did you see the other note on my last post
maybe the log insert fails, and you arent handling that bit correctly.
code for that wasnt in the sample.
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
That's what's weird. It only fails about 50% of the time. Try adding and deleting a few times and see what happens.
I originally suppressed it but I suspected that perhaps it's what's causing the problem. So, I didn't suppress it for the mockup.
Do you mean about contraints?
Correct, I commented out that bit just to make sure and it didn't help.
Thanks
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
Nope...I just noticed that post now! I must have missed it in my posting back to Gemma.
Tonight is fine
Thanks a bunch.
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
Ok, removing the Set..
The code fails when it gets to Me.requery. I get a very helpful "Reserved Error" message. Clicking Help gives a blank page from the extremely useful Microsoft Help documentation.
SHADOW
The code fails when it gets to Me.requery. I get a very helpful "Reserved Error" message. Clicking Help gives a blank page from the extremely useful Microsoft Help documentation.
SHADOW
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
I'm using your code and I found ONE detail that seems to help.
I put the Cancel = True at the end, after the requery. I am wondering if it has trouble writing to a record that is slated for deletion. So, I put the cancel = true at the beginning - i.e. before the me.deleted = true = and it seems to work ok now. The only problem I am facing now is that it won't requery without that error. It does advance to the next record fine, though (if I comment out the requery).
Makes sense. When I think about it, I would not do a requery in a BeforeUpdate event, so by same logic, it shouldn't be done in the Delete event (that's before the actual deletion, right? And AfterDel fires before that, right?) My thought was to hold the bookmark value until the Delete event is all done then run a requery... that part I've not figured exactly how.
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
shadow9449
Registered User.
- Local time
- Yesterday, 20:59
- Joined
- Mar 5, 2004
- Messages
- 1,037
Ok, looking forward to hearing...
After some experimentation, I've concluded the following:
1) It is possible to make use of Delete event to restrict what records can or can't be deleted, save the record, or move to another record.
2) It is not possible to do a requery because once we're in Delete/BeforeDelConfirm/AfterDelConfirm, Access already has opened a transaction and is holding the record in a buffer. If we want to do requery, it will have to wait until after the fact.
3) If any other events that are fired during the Delete events are pending (e.g. saving a record in a Delete event), #2 still applies. Thus if any other events that are called due to operation executed in Delete requires an action that isn't supported in a transaction, it will fail as well.
4) There are no events *after* the deletion has been completed and the buffer closed. Thus the firing sequence is:
Delete -> BeforeDelConfirm -> AfterDelConfirm
and either Delete and BeforeDelConfirm can be used to cancel deletions (but I've observed strange results with BeforeDelConfirm - see below)
What happens after AfterDelConfirm is anyone's guess.... Current event does not necessarily fire right after AfterDelConfirm, so it can't be used to requery when passed a flag from the Deletion.
Also, if Delete event is cancelled, neither BeforeDelConfirm nor AfterDelfConfirm will be fired, but if BeforeDelConfirm is cancelled, AfterDelConfirm still fires regardless. Regardless of the actual firing sequence, a buffer is open in all three events (or just one event where Delete event is canclled)
5) BeforeDelConfirm event is special. If it's cancelled, the deletions are aborted, yes, but any edits or changes made between the Delete event and BeforeDelConfirm seems to be rollbacked, and the record is removed from the form's recordset (but not from table). I believe this is flaw in Access.
6) When we enter the delete sequence, Access opens a transaction. That transaction is not accessible (e.g. I cannot force a rollback or commit upon that transaction) and it's open until *after* the AfterDelConfirm event has fired, which is too late to do anything.
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.
1) It is possible to make use of Delete event to restrict what records can or can't be deleted, save the record, or move to another record.
2) It is not possible to do a requery because once we're in Delete/BeforeDelConfirm/AfterDelConfirm, Access already has opened a transaction and is holding the record in a buffer. If we want to do requery, it will have to wait until after the fact.
3) If any other events that are fired during the Delete events are pending (e.g. saving a record in a Delete event), #2 still applies. Thus if any other events that are called due to operation executed in Delete requires an action that isn't supported in a transaction, it will fail as well.
4) There are no events *after* the deletion has been completed and the buffer closed. Thus the firing sequence is:
Delete -> BeforeDelConfirm -> AfterDelConfirm
and either Delete and BeforeDelConfirm can be used to cancel deletions (but I've observed strange results with BeforeDelConfirm - see below)
What happens after AfterDelConfirm is anyone's guess.... Current event does not necessarily fire right after AfterDelConfirm, so it can't be used to requery when passed a flag from the Deletion.
Also, if Delete event is cancelled, neither BeforeDelConfirm nor AfterDelfConfirm will be fired, but if BeforeDelConfirm is cancelled, AfterDelConfirm still fires regardless. Regardless of the actual firing sequence, a buffer is open in all three events (or just one event where Delete event is canclled)
5) BeforeDelConfirm event is special. If it's cancelled, the deletions are aborted, yes, but any edits or changes made between the Delete event and BeforeDelConfirm seems to be rollbacked, and the record is removed from the form's recordset (but not from table). I believe this is flaw in Access.
6) When we enter the delete sequence, Access opens a transaction. That transaction is not accessible (e.g. I cannot force a rollback or commit upon that transaction) and it's open until *after* the AfterDelConfirm event has fired, which is too late to do anything.
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.
Last edited:
AFter saying it couldn't be done, I had an idea. 
This seems to work, though the record doesn't go where I want it to but am out of time. Here's the prototype code. See if you can fix the bookmarking issue so the record get moved to the immediate next record from the "deleted" record. Keep in mind, of course that the code will error out if you're on the last record because I didn't handle for new record being the next record.
Note the use of Timer event to "guarantee" the firing immediately after the delete event (and after the buffer that Access holds has been cleared to allow us to requery). The timing is only 1 millisecond, which in my test was sufficient for Access to drop the delete buffer... A slightly higher value may be needed for older computer, though.
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...
This seems to work, though the record doesn't go where I want it to but am out of time. Here's the prototype code. See if you can fix the bookmarking issue so the record get moved to the immediate next record from the "deleted" record. Keep in mind, of course that the code will error out if you're on the last record because I didn't handle for new record being the next record.
Code:
Private Sub Form_Delete(Cancel As Integer)
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.MoveNext
v = .Bookmark
End With
Me.Deleted = True
Me.Dirty = False
Me.TimerInterval = 1
Cancel = True
End Sub
Private Sub Form_Timer()
Me.TimerInterval = 0
Me.Requery
Me.Bookmark = v
End SubNote the use of Timer event to "guarantee" the firing immediately after the delete event (and after the buffer that Access holds has been cleared to allow us to requery). The timing is only 1 millisecond, which in my test was sufficient for Access to drop the delete buffer... A slightly higher value may be needed for older computer, though.
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...
