Attempt to delete subform record returns "No current record"

0
votes

Using Access 2010. I have two subforms from and to. I have two buttons on the main form, addRecord which adds a new record to to using values from from's currently selected record and from the main form, and deleteRecord which deletes to's currently selected record.

Here is the code for the two buttons:

Private Sub addRecord_Click()

Dim contactid As Long
Dim requestid As Long
Dim startDate As Date

contactid = Me.from_subform.Controls("contactID").Value
requestid = Me.ID.Value
startDate = Me.startDate.Value

With Me.to_subform.Form.RecordsetClone
    .AddNew
    !AEid = contactid
    !requestid = requestid
    !startDate = startDate
    .Update
End With

Me.to_subform.Form.Requery
Me.from_subform.Form.Requery

End Sub

Private Sub deleteRecord_Click()

If Me.to_subform.Form.RecordsetClone.RecordCount = 0 Then
    Exit Sub
End If

Me.to_subform.Form.Recordset.Delete
Me.to_subform.Form.Recordset.MoveNext
Me.from_subform.Form.Requery


End Sub

from gets requeried because some of its fields depend on whether or not there is a corresponding record in to.

The problem is that when I have one record in to, and then I add another one using addRecord, when I try to delete the first record with deleteRecord, I get the following error:

Run-time error '3021':
No current record.

I can select any other record in to besides the first one, and delete it; also, if to is empty and I add a single record to it, then I can delete it. And once I delete another record in to, I can select that first one and delete it.

How can I delete that first record without having to delete another one first?

EDIT: examining the recordset further in the debugger, when I get the No current record error, .BOF and .EOF are both False, but .AbsolutePosition is -1.

2
ms-accessvba

2 Answers

0
votes

you could try

Me.to_subform.Form.Recordset.MoveFirst

Before the delete but may I suggest that a simpler way to add and delete records is via SQL

Currentdb.execute "DELETE * FROM TheTable WHERE ContactId=" & contactId
Me.from_subform.Form.Requery
0
votes

Ok.

After an exhaustive search of everything 3021-related, I decided to stop messing around with deleting items from the recordset. Instead, I'm using the following code to delete the currently selected record:

Private Sub deleteBtn_Click()

Dim db As dao.Database
Dim rs As Recordset
Dim id As Long

'test for an empty subform
Set rs = Me.to_subform.Form.RecordsetClone
If rs.RecordCount = 0 Then
    Exit Sub
End If


'test for no record selected (i.e. cursor is on the new record line)
If Not IsNull(Me.to_subform.Controls("id").Value) Then

    'get the ID value of the selected record
    id = Me.to_subform.Controls("id").Value

    'delete it from the source table
    Set db = CurrentDb
    db.Execute "delete * from toTable where id=" & id, dbFailOnError

    'refresh both forms
    Me.to_subform.Form.Requery
    Me.from_subform.Form.Requery
Else
    MsgBox "No record selected."
End If


End Sub

And it works beautifully.