Delete multiple records in a subform

[gaker10]

I have a form set up with a subform and a Delete function attached to a button. Right now, it only deletes one record at a time. How can I code this to make it delete highlighted records?

Delete function Code:

Private Sub butDelete_Click()


'check for existing record
    If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
    
        If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
'yes on confirm: delete
            CurrentDb.Execute "DELETE FROM EntryFormTable " & _
            " WHERE Component='" & Me.frmEntrySub!Component & "'"
'refresh form
            Me.frmEntrySub.Form.Requery
        End If
    End If
    
End Sub

[June7]

I do this with a subform in datasheet view and Delete button on main form. Here is an extract of my code behind main form:

Option Compare Database
Option Explicit
Dim intHeight As Integer    'stores value for number of tests selected for deletion
Dim intTop As Integer   'stores value for position of the first selected record in Tests recordset

Private Sub ctrTests_Exit(Cancel As Integer)
'sets module variables starting values for use in deleting selected tests
intHeight = Me.ctrTests.Form.SelHeight
intTop = Me.ctrTests.Form.SelTop
End Sub

Private Sub RemoveTests_Click()
Dim N As Integer
With Me.ctrTests.Form.RecordsetClone
If .RecordCount < 1 Then
    MsgBox "No tests have been saved.  Delete action canceled.", , "RemoveTest Error"
ElseIf intHeight < 1 Then
    MsgBox "No tests have been selected.  Delete action canceled.", , "RemoveTest Error"
ElseIf MsgBox("This action may delete any saved test data.  Proceed?", vbExclamation + vbOKCancel, "Delete Test?") = vbOK Then
   For N = 1 To intHeight
      .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
      .MoveFirst
      CurrentDb.Execute "DELETE FROM Tests WHERE LabNum='" & Me.tbxLABNUM & "' AND TestNum='" & !TestNum & "'"
      Me.ctrTests.Requery
   Next
End If 
End With
End Sub

Code behind the subform to disable keyboard delete:
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
'suppress default Delete Confirm dialog box.
Response = acDataErrContinue
'cancel the automatic delete operation
Cancel = True
MsgBox "Must click Remove Test button to delete test from sample."
End Sub

Making me look at this again has me thinking about changing so the delete code is behind the subform and the main form button calls the subform procedure.

[gaker10]

The problem with my Edit and Delete functions right now is it is dependent on the Component Tag. Since I got my alternates working (which must allow for duplicate components), it will edit/delete all records with that component, which is a logical error. Id rather delete and Edit records based on a highlighted record independent of the component tag.

[June7]

Is there a unique record ID? An autonumber field could serve that purpose.

[gaker10]

Yeah but the only purpose that serves right now is to keep order by entry. At some point I'm going to implement code to reset the count to 1 for every assembly or quote.

[June7]

Can probably use the ID to uniquely identify record for deletion.

[gaker10]

Where would those ID references go in your code?

[June7]

Try:

CurrentDb.Execute "DELETE FROM EntryFormTable WHERE ID=" & !ID

[gaker10]

Got a run-time error '3167': Record is deleted.

Code:

Private Sub butDelete_Click()


With Me.frmEntrySub.Form.RecordsetClone
If .RecordCount < 1 Then
    MsgBox "No tests have been saved.  Delete action canceled.", , "DeleteRecord Error"
ElseIf intHeight < 1 Then
    MsgBox "No tests have been selected.  Delete action canceled.", , "DeleteRecord Error"
ElseIf MsgBox("This action may delete any saved test data.  Proceed?", vbExclamation + vbOKCancel, "Delete Record?") = vbOK Then
   For N = 1 To intHeight
      .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
      .MoveFirst
      CurrentDb.Execute "DELETE FROM EntryFormTable WHERE ID=" & !ID
   Next
End If
End With
End Sub

[June7]

Don't know why that causes error. Is there more info in that error message? Is that its full text?

[gaker10]

This is the result. I tried to delete the highlighted lines.

[June7]

I had to delete a lot of lines from my code that would be irrelevant to you. Appears I should have kept one. I have edited my earlier post. After the DELETE action, try:

Me.frmEntrySub.Requery

[gaker10]

Different run-time error '3420': Object invalid or no longer set.

Code:

Private Sub butDelete_Click()


With Me.frmEntrySub.Form.RecordsetClone
If .RecordCount < 1 Then
    MsgBox "No tests have been saved.  Delete action canceled.", , "DeleteRecord Error"
ElseIf intHeight < 1 Then
    MsgBox "No tests have been selected.  Delete action canceled.", , "DeleteRecord Error"
ElseIf MsgBox("This action may delete any saved test data.  Proceed?", vbExclamation + vbOKCancel, "Delete Record?") = vbOK Then
   For N = 1 To intHeight
      .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
      .MoveFirst
      CurrentDb.Execute "DELETE FROM EntryFormTable WHERE ID=" & !ID
      Me.frmEntrySub.Requery
   Next
End If
End With
End Sub

[June7]

I suspect you did not declare intHeight and intTop in the module header as shown in my example.

[gaker10]

Option Compare Database
Dim intHeight As Integer    'stores value for number of records selected for deletion
Dim intTop As Integer   'stores value for position of the first selected record in recordset
Dim N As Integer
Dim X As Double
Dim Y As Double