Problem with deleting record from recordset

[hansu]

I use the following code to delete a record from the recordset rstOrders.
rstOrders contains just one record. The record is taken from an Access DB.
Cursor type is adOpenStatic, adLockOptimistic.

Private Sub cmdDelete_Click()
Dim intAnswer As Integer
  On Error GoTo DeleteErr

  intAnswer = MsgBox("Do you really want to delete this order?", vbYesNo + vbQuestion, "Delete order")
  If intAnswer = vbNo Then
  	Exit Sub
  Else
  	With rstOrders
    	  .Delete
  	End With
  End If
  Unload Me
  Exit Sub
  
DeleteErr:
  MsgBox Err.Description
End Sub

The code works fine on the developing machine. On the client machine it generates the error
"Multiple-Step Operation Generated Errors. Check Each Status Value". If the update method
is called on rstOrders before delete, then it runs without error.
Is it necessary to make a move after delete?
Thanks for your help.

 

[pokermat]

I would check that the client has an up to date version of ADO.

Also are you editing the record elsewhere in the code?

 

[SilverSide]

"adOpenStatic"

try open Dynamic

and I dont remember but I think a .Move 0 (zero)
should be used too

®od

 

[hansu]

Thanks for your tips.
I had to set:

.bookmark = .bookmark

before the delete statement.
It works now.

 

[CCLINT]

hansu: Are you using a client or server cursor(default is server)?
Try using UpdateBatch first. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[hansu]

Hi CCLINT
I'm using a client-side cursor. I receive the same error when I do a UpdateBatch before delete. It seems that HROW points to a wrong row. When I set the Bookmark as mentionend above, or if I do a .MoveLast it works.

 

[CCLINT]

>Is it necessary to make a move after delete?

(Somehow I missed this remark of yours. The error is associated with several causes.)

Yes! After a deletion you need to move off the deleted record.

You have to develop a logic to do this, because you do not know if after the deletion the cursor is at the end of the recordset, or the beginning, or if possibly there are no more records in the recordset(This should not be needed if you are using a complex data bound object such as a DataGrid):

.Delete
'Move off of the deleted record, as it actually still remains the active record
.MovePrevious
'However, if we deleted the first record, then we could be at the BOF. Check for this, and if BOF is True, check if EOF is also True(Meaning that there are no more records), and if not, move to the first record. Otherwise, do nothing, because there are no more records.
If .BOF Then And Not .EOF Then .MoveFirst



[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

You also might want to try calling the rs.CancelUpdate method before the Deletion method.

It is possible that a change has beed made to the record(such as when a DefaultValue on a field is set, and, say, a Date fields contain NULL) with or with-out you knowing it. And then you are trying to delete the record before updating it. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[hansu]

Hello CCLINT
Thanks for your help.
I tried your suggestions but still receive the same error.
If I set the code as follows it works without errors:

  With rstOrders
    If (Not .BOF Or .EOF) Then
        If (.Status <> adRecDBDeleted) Then
         .Bookmark = .Bookmark
         .Delete
        End If
    End If
  End With

rstOrders is a hierarchical recordset with the child recordset shown in a datagrid. As I mentionend before the parent recordset contains always just one record. I found that .BOF and .EOF are always false. So the rowhandle should point to the correct record. But if I don't set the bookmark property the deletion doesn't work. I have some trouble to understand this.