CODEWITHSUNDEEP
vbams-access
Fil
Fil

Reputation: 471

Occasional Error Update or CancelUpdate without AddNew or Edit

I am using a listbox to navigate between records on a form but I occasionally get Error Update or CancelUpdate without AddNew or Edit Run-time Error 3020 on Me.Bookmark = rs.Bookmark. I can't seems to figure out what the problem is and was wondering if someone can help me with how to fix it.

Option Compare Database
Option Explicit
Dim rs As DAO.Recordset

Private Sub listBox_AfterUpdate()
    Dim rs As DAO.Recordset
    If Not IsNull(ItemNo) And Not IsNull(itemName) Then
        Set rs = Me.RecordsetClone
        rs.FindFirst "[ItemNo] = '" & Me![listBox] & "'"
        If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
    Else
        Exit Sub
    End If
    
    If IsNull(ItemNo) Or IsNull(itemName) Then
        Exit Sub
    End If
    
    rs.Close
    Set rs = Nothing
End Sub

Upvotes: 0

Views: 899

Answers (2)

Sam
Sam

Reputation: 381

I came across this issue. For me at least, I noticed it would happen under the following conditions:

  1. The form was in edit mode (dirty),
  2. I click the item in the listbox which is the same record as the current record,
  3. if I then make any edit in any field, the error happens as soon as I move away from that field.

I was able to prevent the error either by saving the record before setting Me.Bookmark = rs.Bookmark (as suggested by Olivier Jacot-Descombes) or by comparing the values of Me.Bookmark and rs.Bookmark to make sure they were not the same.

If StrComp(rs.Bookmark, Me.Bookmark, 0) <> 0 Then
    If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
End If

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112402

You have probably started to edit the record in the Form. Save the pending edits before moving to another record.

    If Not rs.NoMatch Then
        Me.Dirty = False  'Saves any pending changes.
        Me.Bookmark = rs.Bookmark  'Navigate to another record.
    End If

Upvotes: 1

Related Questions