Run-time Error 3021- No current record... 1

[Rjc8513]

I have a data entry form named [frm SAMPLING DATA]. The record source for the form is the table [tbl SAMPLING DATA]. The form also contains a subform [frm SAMPLING DATA subform] in datasheet view. The record source for the subform is a query [sqry SAMPLING DATA TEMP] which is linked to the table [tbl SAMPLING DATA TEMP].

The user enters data on the main form, clicks an 'Add' button and the data entered appears in the subform. After all data is entered for the current session, the user clicks another button to post the data to the main database and the subform is cleared.

The user has the ability to delete a record from the subform before posting to the main database by selecting the record in the datasheet view and clicking a 'Delete' button on the main form. First a message box appears, asking the user to confirm the delete.

This works fine until I attempt to delete the last remaining record in the datasheet view of the subform. When I attempt to delete the last record I get the runtime error 3021, 'No current record'.

Any ideas? Thanks (below is the code assigned to the 'Delete' button).

Private Sub cmd_DELETE_Click()

Dim dbs As DAO.Database
Dim rstTemp As DAO.Recordset

Set dbs = CurrentDb
Set rstTemp=Me.frm_SAMPLING_DATA_subform.Form.RecordsetClone

If Not rstTemp.RecordCount = 0 Then
rstTemp.Bookmark = Me.frm_SAMPLING_DATA_subform.Form.Bookmark
theDay = rstTemp.Fields("DAY").Value
theMonth = rstTemp.Fields("MONTH").Value
theDate = rstTemp.Fields("DATE").Value
theYear = rstTemp.Fields("YEAR").Value

Response = MsgBox("Are you sure you want to delete this record?" + Chr(13) + Chr(13) + _
Chr(13) + "DAY OF WEEK: " & theDay + Chr(13) + _
Chr(13) + "DATE: " & theMonth & " " + theDate & ", " + theYear + Chr(13) + _
Chr(13), vbYesNo + vbQuestion, "DELETE RECORD?")

If Response = vbNo Then
Exit Sub
End If

Me.frm_SAMPLING_DATA_subform.Form.Recordset.Delete
Me.Requery

End If

End Sub

 

[VBslammer]

The AbsolutePosition is probably at -1 if you don't adjust it after the delete. You can work with the subform directly if you reference it, which makes it easier to code:

Private Sub cmdDelete_Click()
  Dim frm As Form
  Set frm = Me.frm_SAMPLING_DATA_subform.Form
  
  If frm.NewRecord Then
    MsgBox "Cannot delete a new record", vbInformation, "No Record"
  Else
    If MsgBox("Delete record " & frm!Day & "?", _
        vbQuestion + vbOKCancel, "Verify") = vbOK Then
      frm.Recordset.Delete
      If Not (frm.Recordset.BOF And frm.Recordset.EOF) Then
        frm.Recordset.Move 1
      End If
    End If
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[ZmrAbdulla]

Also you can add this code to the Current Event of the SubForm to enable/disable the command buttons , if they are on the form. You need to change the form/command names where necessary.

Private Sub Form_Current()
         If Me.NewRecord Then  
    Forms!MasterForm!CmdDelete.Enabled = False [COLOR=green]'command on mainform[/color]
        Else   
    Forms!MasterForm!CmdDelete.Enabled = True [COLOR=green]'command on mainform
[/color]    End If    
End Sub

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[Rjc8513]

Thanks slammer, that did it!

This is the best forum on God's green earth.