Error only on accde but not accdb

[calvinle]

Hi,

I have a form set to continuous, and on that form header, I have a :
Combobox (to filter the continuous form)
Command button (to clear the filter)
Command button (to add new record)
In the continuous data, there is a command button to delete that selected record.

The user has to choose which member in the combobox, then add a new record that related to that member only.

After_Update of the Combobox:

   Dim strWhere As String                  'The criteria string.
  Dim lngLen As Long
   
  If Not IsNull(Me.cboMember) Then
    strWhere = strWhere & "([member_id] = " & Me.cboMember & ") AND "
  End If
   lngLen = Len(strWhere) - 5
  If lngLen <= 0 Then
    Me.Form.Filter = ""
    Me.Form.FilterOn = False
  Else
    strWhere = Left$(strWhere, lngLen)
     Me.Form.Filter = strWhere
    Me.Form.FilterOn = True
  End If

Click of Command button "Add New Record":

   If Nz(Me.cboMember) = "" Then MsgBox "You need to choose a [Member] first!", , "": Exit Sub
  
  Set rs = Me.RecordsetClone
  
  rs.AddNew
  rs![member_id] = Nz(Me.cboMember)
  rs![amount] = Nz(Me.cboMember.Column(1))
  rs![items] = Me.cboMember.Column(2) 'sChequePolicy
  rs.Update
  
  Set rs = Nothing
  
  DoCmd.GoToRecord , , acLast
  
  Me.payment.SetFocus

When adding a new record, there will be a command button on that record, which if they click, it will delete that record:

 On Error GoTo Err_Handler
   iMsgboxAnswer = MsgBox(Prompt:="Are you sure you want to delete this cheque?", Buttons:=vbYesNo, Title:="")
  
  If iMsgboxAnswer = vbYes Then
    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.Delete
    Me.Recalc
  End If
  
 Exit_Handler:
    Exit Sub
 Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDuplicateCheque_Click"
    Resume Exit_Handler

Now, here comes the problem about accdb vs accde. If the user choose a member in combobox, then add a new record, delete that record, then click on the next clear filter button:

   Dim strWhere As String                  'The criteria string.
  Dim lngLen As Long
  
  Me.cboMember = Null
  
  If Not IsNull(Me.cboMember) Then
    strWhere = strWhere & "([member_id] = " & Me.cboMember& ") AND "
  End If
   lngLen = Len(strWhere) - 5
  If lngLen <= 0 Then
    Me.Form.Filter = ""
    Me.Form.FilterOn = False
  Else
    strWhere = Left$(strWhere, lngLen)
     Me.Form.Filter = strWhere
    Me.Form.FilterOn = True
  End If

In accdb, there is no error. In accde, there will be an error saying Record is delete#.

Does anyone can figure out where is the gap in my code?

Thanks

 

[arnelgp]

do you also have an autonumber field in the form?
if yes, you can save it and requery the form after deleting.
then go back to the saved autonumber field.
assuming ID is your pk key:

Dim rs As DAO.Recordset
Dim varID As Variant
Dim varBookMark As Variant
Dim bolOK As Boolean
 
 On Error GoTo Err_Handler
   iMsgboxAnswer = MsgBox(Prompt:="Are you sure you want to delete this cheque?", Buttons:=vbYesNo, Title:="")
  
  If iMsgboxAnswer = vbYes Then
    varBookMark = Me.Bookmark
    'set bookmark same to form
    Set rs = Me.RecordsetClone
    rs.Bookmark = varBookMark
    ' move next
    rs.MoveNext
    If Not rs.EOF Then
        bolOK = True
        varID = rs![ID]
    End If
    If Not bolOK Then
        rs.Bookmark = varBookMark
        rs.MovePrevious
        If Not rs.BOF Then
            bolOK = True
            varID = rs![ID]
        End If
    End If
    'go again to bookmark and delete this record
    rs.Bookmark = varBookMark
    rs.Delete
    Set rs = Nothing
    'requery the form/subform
    Me.Requery
    'get the new recordset
    Set rs = Me.RecordsetClone
    If bolOK Then
        'go to our record
        rs.FindFirst "[id] = " & varID
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
  End If
  
Exit_Handler:
    Exit Sub
Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDuplicateCheque_Click"
    Resume Exit_Handler

 

[calvinle]

Why so long code just for a delete action?

I have tried tochange the delete code to:
RunCommand Delete

And I don't have that error anymore. Just wondering if that RunCommand Delete code can harm the process?

Thanks