How do I code delete a record from a combo Box with a button (1 Viewer)

[Tom d]

I have a Combo box on the 'Authors FRM' and a button to delete the selected record. How do I code the button to delete the selected record??

 

[LarryE]

Name the button DeleteRecord, then use the following code:

Private Sub DeleteRecord_Click()
Dim Reply As Variant
Reply = MsgBox("THIS ACTION DELETES THIS RECORD." & Chr(10) & Chr(10) & "DO YOU WANT TO DO THIS?", vbYesNo)
If Reply = 7 Then 'NO
    DoCmd.CancelEvent
    Exit Sub
End If
If Reply = 6 Then 'Yes
    DoCmd.RunCommand acCmdDeleteRecord
    Me.Requery
End If
End Sub

 

[MarkK]

Just to show some other options...

Private Sub DeleteRecord_Click()
    Dim rsp As VbMsgBoxResult
    
    rsp = MsgBox( _
        "THIS ACTION DELETES THIS RECORD." & vbCrLf & "DO YOU WANT TO DO THIS?", _
        vbYesNo + vbExclamation + vbDefaultButton2, _
        "Confirm Delete")
    If rsp = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
        Me.Requery
    End If
End Sub

• The MsgBox() function returns a member of the vbMsgBoxResult enumeration, which can make the code a little clearer.
• For Deletes, I like to set the default button to <No> using vbDefaultButton2, so the user has to to explicitly click <Yes>.
• You don't have to handle the vbNo (7) case. If it's not vbYes, the delete will not occur, and nothing changes.
: )

 

[Tom d]

Just to show some other options...

Private Sub DeleteRecord_Click()
    Dim rsp As VbMsgBoxResult
   
    rsp = MsgBox( _
        "THIS ACTION DELETES THIS RECORD." & vbCrLf & "DO YOU WANT TO DO THIS?", _
        vbYesNo + vbExclamation + vbDefaultButton2, _
        "Confirm Delete")
    If rsp = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
        Me.Requery
    End If
End Sub

• The MsgBox() function returns a member of the vbMsgBoxResult enumeration, which can make the code a little clearer.
• For Deletes, I like to set the default button to <No> using vbDefaultButton2, so the user has to to explicitly click <Yes>.
• You don't have to handle the vbNo (7) case. If it's not vbYes, the delete will not occur, and nothing changes.
: )

Code does not work

 

[Tom d]

Code does not work
View attachment 116720

Delete does not work on the Authors Entry screen, the delete button

 

[Gasman]

I am not sure what a combo box has to do with anything, but I am guessing you have not described your issue correctly.
Are you expecting to delete that entry in the combo?

 

[Tom d]

I am not sure what a combo box has to do with anything, but I am guessing you have not described your issue correctly.
Are you expecting to delete that entry in the combo?

Yes I want to delete the selected item in the combo box

 

[Gasman]

And is the combo recordsource a query?

 

[DickyP]

Yes I want to delete the selected item in the combo box

I was just about to ask that!!

 

[Pat Hartman]

I question the logic of using the combo to select the record to be deleted from the RowSource of the combo.

 

[Tom d]

And is the combo recordsource a query?

 

[XPS35]

It depends on whether you want to remove an author even if there are related books. If you do want that, the books must also be removed. To achieve this, you must adjust a property of the relationship between authors and books (enable cascade delete). You can then delete an author and the associated books with the following code:

Private Sub Delete_But_Click()
    Dim strSQL As String
    
    If MsgBox("Are you sure you want to delete this author?", vbYesNo) = vbYes Then
        strSQL = "DELETE * FROM [Authors TBL] WHERE AuthorsID = " & AuhorsNameCBO
        DoCmd.RunSQL (strSQL)
        Me.AuhorsNameCBO.Requery
    End If
    
End Sub

If an author for whom books are present in the database cannot be removed, then it is a different story. Then please let us know.

 

[Solo712]

It depends on whether you want to remove an author even if there are related books. If you do want that, the books must also be removed. To achieve this, you must adjust a property of the relationship between authors and books (enable cascade delete). You can then delete an author and the associated books with the following code:

Private Sub Delete_But_Click()
    Dim strSQL As String
   
    If MsgBox("Are you sure you want to delete this author?", vbYesNo) = vbYes Then
        strSQL = "DELETE * FROM [Authors TBL] WHERE AuthorsID = " & AuhorsNameCBO
        DoCmd.RunSQL (strSQL)
        Me.AuhorsNameCBO.Requery
    End If
   
End Sub

If an author for whom books are present in the database cannot be removed, then it is a different story. Then please let us know.

The relationship between authors and books has been set, so the delete will not happen if there are books by the author.

Best,
Jiri

 

[Tom d]

Yes, Built by the system

 

[arnelgp]

when you select an Item on the combobox, you also need to goto that AuthorsID on the recordset of the form.

 

[Tom d]

Just to show some other options...

Private Sub DeleteRecord_Click()
    Dim rsp As VbMsgBoxResult
   
    rsp = MsgBox( _
        "THIS ACTION DELETES THIS RECORD." & vbCrLf & "DO YOU WANT TO DO THIS?", _
        vbYesNo + vbExclamation + vbDefaultButton2, _
        "Confirm Delete")
    If rsp = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
        Me.Requery
    End If
End Sub

• The MsgBox() function returns a member of the vbMsgBoxResult enumeration, which can make the code a little clearer.
• For Deletes, I like to set the default button to <No> using vbDefaultButton2, so the user has to to explicitly click <Yes>.
• You don't have to handle the vbNo (7) case. If it's not vbYes, the delete will not occur, and nothing changes.
: )

Received error message "Invalid Outside Procedure"

 

[Gasman]

On which line?
That code needs to be in a form as well?

 

[LarryE]

If you are going to use a single button to delete a record based upon a combo box, then you need to use a DELETE Action query to isolate the selected record and then delete it. Here is your file using that procedure. I deleted 2 test records. It still uses a message to confirm or cancel the operation.

 

[Gasman]

I would probably use the Execute, so as not to get the warnings.

One way.

Private Sub Delete_But_Click()
Dim Reply As Variant
Dim strSQL As String

Reply = MsgBox("THIS ACTION DELETES THIS RECORD." & Chr(10) & Chr(10) & "DO YOU WANT TO DO THIS?", vbYesNo)
If Reply = 7 Then 'NO
    DoCmd.CancelEvent
    Exit Sub
End If
If Reply = 6 Then 'Yes
    'DoCmd.OpenQuery "DeleteAuthor", acViewNormal, acReadOnly
    strSQL = "DELETE [Authors TBL].AuthorsID, [Authors TBL].AuthorsName FROM [Authors TBL]"
    strSQL = strSQL & " WHERE [Authors TBL].AuthorsID = " & Me.AuhorsNameCBO
    Debug.Print strSQL
    CurrentDb.Execute strSQL
    Me.Requery
    Me.Recalc
End If

End Sub

 

[LarryE]

I would probably use the Execute, so as not to get the warnings.

One way.

Private Sub Delete_But_Click()
Dim Reply As Variant
Dim strSQL As String

Reply = MsgBox("THIS ACTION DELETES THIS RECORD." & Chr(10) & Chr(10) & "DO YOU WANT TO DO THIS?", vbYesNo)
If Reply = 7 Then 'NO
    DoCmd.CancelEvent
    Exit Sub
End If
If Reply = 6 Then 'Yes
    'DoCmd.OpenQuery "DeleteAuthor", acViewNormal, acReadOnly
    strSQL = "DELETE [Authors TBL].AuthorsID, [Authors TBL].AuthorsName FROM [Authors TBL]"
    strSQL = strSQL & " WHERE [Authors TBL].AuthorsID = " & Me.AuhorsNameCBO
    Debug.Print strSQL
    CurrentDb.Execute strSQL
    Me.Requery
    Me.Recalc
End If

End Sub

I would probably not use a combo box either. I would use a search textbox to find the record and then use a delete query with the selected record ID as the criteria. But I like to actually view the record I am deleting, but that's just me. Using a combo box is a little dangerous, especially if you have multiple users.