Run time error 3021- no current record for delete button

0
votes

I have the following code for a delete button written in visual basics in Microsoft Access.

Private Sub Delete_Click()
If Not (Me.ComputerSubform.Form.Recordset.EOF And Me.ComputerSubform.Form.Recordset.BOF) Then
    If MsgBox("Are you sure to delete?", vbYesNo) = vbYes Then
        CurrentDb.Execute "DELETE FROM Computer " & _
                " WHERE PCSN=" & Me.ComputerSubform.Form.Recordset.Fields("PCSN")
      Me.ComputerSubform.Form.Requery    
    End If
   End If
End Sub

The first time it words fine. But when I try to delete another record I will encounter "Run time error 3021- no current record". I could not understand as the code look fines to me and there is data available. I would appreciate any help. Thanks!

P.S. I am sorry I can't post my table as it contain alot of confidential data.

2
ms-access
After deleting a record in a MS-Access recordset, I believe you need to move to the next row in order to access it successfully. It has been awhile since I have solved this problem, so some trial and error may be required. - ron tornambe
@ron tornambe Thanks! You are right moving to the next row will not encounter this problem. Is there a coding method to solve it? - user292965
Try @dbmitch answer, but I think that may bring you to the top of the rs. You may also try Me.ComputerSubform.Form.MoveNext - ron tornambe
I tried @dbmitch and your suggestion and it work perfectly. :) Previously, my PSCN column is a number but I need to change it to text now. I am encountering "run time error 3464 data type mismatch in criteria expression text". I know this is a simple problem but I am not sure how to resolve it. May I seek your assistance again. Thanks! - user292965

2 Answers

1
votes

Instead of 

Me.ComputerSubform.Form.Requery

You should use
Me.ComputerSubform.Form.Recordset.Requery

This will update the underlying recordset with your latest delete

0
votes

Try this:

dim rs as dao.recordset
set rs = currentdb.openrecordset("computers",  _
dbopendynaset)
rs.findfirst "[pcsn] =  " & _ 
me.computersubform.form!pcsn
'If pcsn is text instead of a number you need chr(34)
' on either side
if rs.nomatch = false  then 
  rs.edit
  rs.delete 
  rs.update
  rs.close
me.refresh
end if