Insert Error using .addnew twice in a row (DAO) 1

[ron525]

Would really appreciate an opinion on this...
I am accessing a BTrieve file via Linked Table in Access.
I browse the file via a bound Access form which has a
button. Upon clicking the button the routine below runs.

The .edit operation works.
the next .addnew operation works.
the next .addnew operation fails (generic ODBC insert error).

Thank you in advance for any help that can be offered.

Ron D'Amico

Private Sub Command3_Click()
On Error GoTo Err_DeleteandAddNew_Click

Dim rstold As DAO.Recordset
Dim iIndex As Long
Dim firstpart As String
Dim secondpart As String
Dim thirdpart As String

firstpart = Me.txtnewMN_No.Value & "0000"
secondpart = Me.txtnewSb_No.Value & "0000"
thirdpart = Me.txtNewDp_No.Value & "0000"

With Me.Recordset
  .Edit
   .Fields("Filler_0001").Value = "Original Before Account Change"
   .Fields("dist_amt").Value = .Fields("dist_amt").Value * (-1)
   .Update
End With

Set rstold = Me.RecordsetClone
rstold.Bookmark = Me.Recordset.Bookmark

With Me.Recordset
  .addnew
    For iIndex = 0 To rstold.Fields.Count - 1
        .Fields(iIndex).Value = rstold.Fields(iIndex).Value
    Next
    .Fields("dist_amt").Value = Me.Recordset.Fields("dist_amt").Value * (-1)
    .Fields("Mn_No").Value = firstpart
    .Fields("sb_No").Value = secondpart
    .Fields("Dp_no").Value = thirdpart
    .Fields("Filler_0001").Value = "Updated Account"
Me.Recordset.Update
End With

Me.Recordset.Bookmark = rstold.Bookmark
'have also tried using .lastmodified above without success

'***************THIS ADD FAILS*************
With Me.Recordset
.addnew
    For iIndex = 0 To rstold.Fields.Count - 1
    .Fields(iIndex).Value = rstold.Fields(iIndex).Value
    Next
        .Fields("dist_amt").Value = Me.Recordset.Fields("dist_amt").Value * (-1)
        .Fields("Mn_No").Value = firstpart
        .Fields("sb_No").Value = secondpart
        .Fields("dp_no").Value = thirdpart
        .Fields("Filler_0001").Value = "Offset"
Me.Recordset.Update
End With

Me.Requery
Me.Recordset.MoveFirst
Exit Sub

Err_DeleteandAddNew_Click:
  MsgBox Me.Name & ":" & vbCrLf & "error #" & Err.Number & " " & Err.Description & vbCrLf, vbCritical

End Sub

 

[TrojanRabbit]

One or more of the fields in the table may not allow duplicates.

 

[ron525]

That was it! (a 9-field key which is a duplicate of
the first one written). I moved a different value into
one of the fields and no complaints, one edit, two adds. Thank you so much!

Ron