Delete record, requery problems on bound form - No Current Record err 1

[farmgrown]

To set the context (there's a lot going on in this app with lots of code and tables but I have boiled the problem down to this situation):
0. DB is in 2000 format being developed under Access 2003.
1.I have a fairly self-contained subform (no Master/Child in effect) that is in continous mode disaply bound to a single table through a straight on SQL-type statement set on the RecordSource attribute, pretty much like Select Items from Table.
2.I should also note there are controls on the parent form which set filters for the subform - one combo box setting the sort by field and the other setting the criteria (e.g. a box to hold the predicate for a Like "stuff" statement.
And a "go" button which sets Filters On for the subform and a "reset" button to turn it off. (I don't believe this is part of problem but I have read there are sometimes problems associated with filters so included this for completeness). As will be explained below I have worked with turning the filter on and off before the problem actions described below to no avail.

3.There is an unbound "Edit" command button on the Detail section so that each record displayed has a control button to request authorized "editing" actions for that record. The OnClick event routine of that command button does the appropriate checking and allows/disallows various user actions including the deletion of the current record. That's where the problem is showing up.
4. when the user clicks the Edit button, the Click routine checks the record and determines based on status/type of record what the user can do. In the case where the only option the user has is to delete the record, a YewNo MsgBox
is popped up to confirm they want to delete.
If they click yes, the Click routine code deletes the current record with:
Me.Recordset.Delete
and the record is deleted. At this point, understandably the Me.Recordset record position is indeterminate (i.e. Me.CurrentRecord or Me.AbsolutePostion are undefined). The
next line of code does a:
Me.Requery
so that the form will refresh the display and by default the focus returns to the first record of the resulting refresh. If I check the FORM's Me.CurrentRecord, it is fine pointing to #1 (fields can be edited, etc). However, randomly and intermittently, the underlying RecordPosition linked to the table is NOT fine and stays indeterminate (i.e. Me.Recordset.CurrentRecord and Me.AbsolutePosition are Null and -1 resp). The next time any action is attempted on the record, there is a "No Current Record" error displayed.
Here's some of the things I have played with:
1. Turning off the Forms filtering before deleting, after deleting but before requerying after the requerying, etc - no change.
2. I have put a for loop delay after the Delete thinking there might be a race condition on the delete (i.e. the requery gets there before the delete completes - although I would hope this would be synchronously queued and dealt with in event queue).
3. I also have Me.Requery on the AfterUpdate event but which usually doesn't fire because the programmatic

Boiled down, I want the user to be able to click on a record and after qualifying code, delete that record, refresh the list and be back on another record still linked to the underlying table correctly.

Has anyone seen this kind of behavior? Am I doing something obviously wrong that's staring me in the face and I can't see it.

I understand that this is most explanation and little code but I hope I've conveyed the essence. It would be too unwieldy to splat all the code out. If this doesn't ring a quick bell with anyone, my next step is to try and creae a simple one-or two-field form with button, etc for a boiled down example and see if the behavior duplicates and if so, will post that code.

Any thougths from anyone?

Thanks,

farmgrown

 

[PHV]

How about replacing this:
Me.Recordset.Delete
with this:
DoCmd.RunCommand acCmdDeleteRecord

and then get rid of the requery ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[farmgrown]

PHV,
Thanks for the suggestion. When I try it, I get the message
"The command or action 'DeleteRecord' is not available now."
in an OK MsgBox....is there some reason why it would not be available during a click event. I can understand if this was a before update event or something like that..Hmmmm.
--Farmgrown

 

[farmgrown]

I created a boiled down database with just the core of the problem and duplicated the problem. This will appear a bit
arcane but it recreates the essence of the problem. The real world has more than kind of reivsion, multiple levels of status and more inter-related fields than a thing with a color but the following database illustrates the problem. The database looks like the following:
Fields
ID Revision Status Description Color
9 (Null) Issued Plane Red
2 Change Modified Plane Green
5 Change Modified Boat Yellow
4 (Null) Issued Boat Yellow
11 (Null) Issued Car Red

You can think of the system as one that handles the issuance of orders to repaint vehicles (i.e. the Car is now painted Red having been issued a paint order, the Plane was last issued and painted as Red but a change order is in to modify the color and the color field has been updated to show the intended color, green. The Boat has a change order started too but it's color still hasn't been decided so it's still yellow). Until an order is issued it can be canceled. The user screen looks like the following:

Id Revision Status Desc Color
Rec1: 9 (Null) Issued Plane Red [Edit btn]
Rec2: 2 Change Modified Plane Green [Edit btn]
..............

The RowSource for form looks like:
Select * From Table1 ORDER BY Description.
The only code is the OnClick event which looks like the following:

Private Sub cmdEdit_Click()
On Error GoTo Err_cmdEdit_Click
Select Case Me.Status
Case "Modified"
If MsgBox("Do you want to delete?", vbYesNo) = vbYes Then
Me.Recordset.Delete 'AREA OF BUG
Me.Requery
'DoCmd.RunCommand acCmdDeleteRecord
End If
Case "Issued"
strDesc = Me.Description
If DCount("[Description]", "[Table1]", "[Description] = '" & strDesc & "'") > 1 Then
MsgBox "There is already a revision pending" & vbCrLf & _
"for this item. Select it to edit."
ElseIf MsgBox("You can't edit an Issued record" & vbCrLf & _
"but you can start a revision." & vbCrLf & _
"Do you want to create a revision?", vbYesNo) = vbYes Then
With Me.RecordsetClone
.AddNew
!Revision = "Change"
!Status = "Modified"
!Description = Me.Description
!Color = Me.Color
.Update
End With
Me.Requery
Else
Me.Undo ' undo any edits to Issued record
End If
End Select

Exit_cmdEdit_Click:
Exit Sub

Err_cmdEdit_Click:
MsgBox Err.Description
Resume Exit_cmdEdit_Click

End Sub

When I run test db with the Me.Recordset.Delete and do the following:
1. click the edit button on the first row item (Plane Issued), and respond Yes to MsgBox, a Change record is added, the list refreshes and focus goes to first row which is now (Plane Change).
2.Then click Edit again (on Plane Change rec), and respond yes to Msgbox to delete and Change record goes away (most of the time).
I can repeat the cycle 1 & 2 above numerous times but on the average of about every dozen times, it won't delete and
I get the No Current Record message.

Now here's the interesting part. I tried your (PHV's) suggestion to use acCmdDeleteRecord and this sample code WILL let me use that action and the bug doesn't seem to occur or if it does it moves out to a much lower frequency (I haven't hit it yet). I am fairly certain that there is no essential difference between the boiled down code and my app but this is acting differently. I thought well perhaps just going back to design view and back to form view or using a debugger change to do it might have an effect but started the database up from scratch with the cmdDeleteRecord in there and it still won't accept it.

So now I'm to more questions. First of all why should there be a difference in .Recordset.Delete and acCmdDeleteRecord and the little example clearly shows that
.Recordset.Delete screws up (a bug???)

And now I'm off to figure out why the acCmdDeleteRecord wont' fly in my real app. Any ideas of what could lock it out. I traced the code in the real app and from the "click of the button through the code, it assigns one variable, goes to the right Select branch, puts up the MsgBox delete confirm and then does the delete. It's got to be some other contextual thing but I have no idea, when they click the edit button there's nothing else going on.

Thanks for your help. If anyone wants me to email them the db, let me know.
--Farmgrown

 

[farmgrown]

I have still not been able to find out why the acCmdDeleteRecord refuses to be available in the app but in surfing around found Allen Brown's suggestion to use the RecordsetClone as shown below which looks like it's doing the trick. Since I can't get CmdDeleteRecord to work and Me.Recordset.Delete is too unreliable, I'm going with this for now. Very unsettling to know that you still didn't find the smoking gun about what's really going on.

The fix was use the following:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.Delete
End With
End If

I put the Dirty and new rec in as protection but from tracing I can tell you that they aren't the culprit. I could put the With ...clone in by itself and that's what changes things.
Some days you just go home shaking your head.....
---Farmgrown