Method 'CancelUpdate' of object' _Recordset' failed

[Nitramiir]

I have encountered a problem which I'm still trying to fix. I'm ADO recordset to communicate with front end (form with textboxes) and main access table. It is as follows;

Setup ado recordset

Private Sub Form_Open(Cancel As Integer)  
    Set rst = CreateObject("ADODB.Recordset")
 
    With rst
      .ActiveConnection = CurrentProject.AccessConnection
      .Source = "SELECT * FROM MapIndex"
      .LockType = 3 'adLockOptimistic
      .CursorType = 1 'adOpenKeyset
      '.CursorLocation = 3 ' adUseClient
      .Open
    End With
 
    Set Me.Recordset = rst
End Sub
 
'Function to add record and save record
Private Sub cmdAddRecord_Click()
 
If cmdAddRecord.Caption = "Add Record" Then
   UnlockRecords
   rst.AddNew
   GivenName.SetFocus
ElseIf cmdAddRecord.Caption = "Save Record" Then
    rst.Update
    LockRecords
End If
End Sub
 
Private Sub cmdEditRecord_Click()
        If cmdEditRecord.Caption = "Cancel" Then
             rst.CancelUpdate
             LockRecords
        End if
 
End Sub

The problem is when I do a rst.CancelUpdate with text written in any textbox it throws a error saying "Multiple-step operation generated errors. Check each status value" then after it shows #Deleted in all the controls. When no text is entered in any field and then rst.CancelUpdate works fine.

Can anyone help as I've been stuck with this error for a few days now. i've already passed the time to hand in database for testing. Thanks heaps.

[NeoPa]

I may be wrong here, but I don't think you want to create a separate recordset and then add it to your form.
You should define a recordset for your form and access that from your code rather than vice-versa.

[Nitramiir]

I may be wrong here, but I don't think you want to create a separate recordset and then add it to your form.
You should define a recordset for your form and access that from your code rather than vice-versa.

Not such want you mean though. I have setup an ADO connection & recordset which I make the form's recordset equal to when the form is opened up. Please explain the vice versa way.

[Nitramiir]

Well it seems you guys dont understand my problem..will find solution elsewhere so much for discussing

[NeoPa]

Not such want you mean though. I have setup an ADO connection & recordset which I make the form's recordset equal to when the form is opened up. Please explain the vice versa way.

The Vice-versa is what you're doing.
You should (I think) be setting the recordsource then accessing the recordset as already set rather than setting your own one.
Is that clear?

[NeoPa]

Well it seems you guys dont understand my problem..will find solution elsewhere so much for discussing

???
I do find you difficult to understand but it is, of course, your prerogative to search elsewhere for answers.
Good luck and best wishes.

[Nitramiir]

Neopa

I managed to solve this problem myself by doing extra coding and not by what you suggested..tried it but did not solve problem. Thanks anyway. yep..its better when you can solve it yourself .

[NeoPa]

NeoPa

I managed to solve this problem myself by doing extra coding and not by what you suggested..tried it but did not solve problem. Thanks anyway. yep..its better when you can solve it yourself .

You're absolutely right. It is much better.
However, if you got that working then I have some learning to do (I was afraid of that).
Would you mind posting your solution in here so that other members and searchers can see your answer. That would be very helpful. I think I also need to look at it and learn when you do.
Well done.