How do I code delete a record from a combo Box with a button

[Tom d]

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

Best,
That is the I want it to work

 

[LarryE]

Did you try the file I sent to you? It deletes records based on the selection you make. Isn't that what you wanted?

 

[Tom d]

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

The problem is that there is a Lost Focus Macro attached to the combo box that saves the record, so that runs first

 

[Tom d]

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

 

[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.
: )

The problem is that there is on lost focus macro attached to the combo box that saves the record if the AuthorsType is <> ''

 

[Tom d]

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 problem is that there is on lost focus macro attached to the combo box that saves the record if the AuthorsType is <> ''

 

[arnelgp]

did you try to test db in post #15 if it will work for you?

 

[Tom d]

The problem is that there is on lost focus macro attached to the combo box that saves the record if the AuthorsType is <> ''

 

[arnelgp]

The problem is that there is on lost focus macro attached to the combo box that saves the record if the AuthorsType is <> ''

test it first, becoz i already did and i want you to see it yourself.

 

[GPGeorge]

test it first, becoz i already did and i want you to see it yourself.

One of my go-to help suggestions has long been, "Try it (yourself) and tell us what happened."

 

[XPS35]

there is on lost focus macro attached to the combo box that saves the record if the AuthorsType is <> ''

As far as I can see AuthorsType is not a control on the form, so Sub AuthorsType_LostFocus() will never be executed.

It seems that you are not doing anything with the solutions offered. I tested my solution (obviously) before posting it. As I already wrote, you have to look at what you do with the cascade delete option of the relationship with the book table.

 

[Gasman]

The problem is that there is on lost focus macro attached to the combo box that saves the record if the AuthorsType is <> ''

Is there a needle stuck here?

 

[gemma-the-husky]

The thing is, do you want to remove an item from a combo box, or remove the item from the database altogether?

These are different things, solved in different ways.

 

[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.
: )

Does not work. Gives error massage on me.Requery. Duplicate key would be created

 

[Tom d]

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

Me.Requery Gives an error of duplicate key would be created

 

[Tom d]

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.

Works except when you key in a name that does not exist a msgbox comes up asking if you want to delete this record i answered no and the record was added. I guess that is a good way to do this, it gives the person a chance to delete the record that was just typed in. How Can you modify a record that is in the table, Example miss spelled a name.

 

[Tom d]

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.

Worked Fine Thank You For all your hard Work. How can a person change a Record, example misspelled name?

 

[Tom d]

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.

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.

Thank You For All Your Hard Work.