Deleting a Record using combobox and command button?

[wolfpack]

As the subject states, I am trying to delete a record using a combobox and a
command button.

I have set up the combobox to allow the user to select a pesrons name from a
table. The combobox gets ReviewerID, LastName, and FirstName.

Now I want to add a command button to delete the record that was chosen.
How do I send the data from the combobox to the button and then what is
needed for the button to delete the record once it is clicked?

Thanks for the help!

 

[Jack Cannon]

The Bound Column on the Combo Box must be set to the ReviewerID.
That is usually 1.
Then it is just a matter of putting these two instructions in the ON_CLICK
event of the button.

CurrentDb.Execute "DELETE FROM MyTable WHERE [ReviewerID]=" &
Me!MyComboBox.Value
Me.Requery

Jack Cannon

 

[wolfpack]

Thanks for the help Jack. That worked.

Now what I tried to do is have a message pop up asking the user if they are
sure they want to delete the record. I can get the message to pop up and when
you click on ok everything goes great. However, when cancel is clicked the
record gets deleted anyhow. How can I prevent this?

Here is my code:

Private Sub Delete_Click()

RetValue = MsgBox("Are you sure you want to remove this reviewer?",
vbOKCancel)

CurrentDb.Execute "DELETE FROM tblReviewers WHERE [ReviewerID]=" &
Me!CmbDeleteReviewer.Value
Me.Requery

DoCmd.Close acForm, "frmDeleteReviewer"

End Sub

The Bound Column on the Combo Box must be set to the ReviewerID.
That is usually 1.
Then it is just a matter of putting these two instructions in the ON_CLICK
event of the button.

CurrentDb.Execute "DELETE FROM MyTable WHERE [ReviewerID]=" &
Me!MyComboBox.Value
Me.Requery

Jack Cannon

As the subject states, I am trying to delete a record using a combobox and a
command button.

I have set up the combobox to allow the user to select a pesrons name from a
table. The combobox gets ReviewerID, LastName, and FirstName.

Now I want to add a command button to delete the record that was chosen.
How do I send the data from the combobox to the button and then what is
needed for the button to delete the record once it is clicked?

Thanks for the help!

 

[Jack Cannon]

I added a Dim statement and an If statement.
I also removed the Requery statement since it is not needed given that you
are closing the form anyway.
This should work for you.


Private Sub Delete_Click()

Dim RetValue

RetValue = MsgBox("Are you sure you want to remove this reviewer?",
vbOKCancel)

If RetValue = vbOK then
CurrentDb.Execute "DELETE FROM tblReviewers WHERE [ReviewerID]=" &
Me!CmbDeleteReviewer.Value
End If

DoCmd.Close acForm, "frmDeleteReviewer"

End Sub

Jack Cannon

Thanks for the help Jack. That worked.

Now what I tried to do is have a message pop up asking the user if they are
sure they want to delete the record. I can get the message to pop up and when
you click on ok everything goes great. However, when cancel is clicked the
record gets deleted anyhow. How can I prevent this?

Here is my code:

Private Sub Delete_Click()

RetValue = MsgBox("Are you sure you want to remove this reviewer?",
vbOKCancel)

CurrentDb.Execute "DELETE FROM tblReviewers WHERE [ReviewerID]=" &
Me!CmbDeleteReviewer.Value
Me.Requery

DoCmd.Close acForm, "frmDeleteReviewer"

End Sub

The Bound Column on the Combo Box must be set to the ReviewerID.
That is usually 1.
Then it is just a matter of putting these two instructions in the ON_CLICK
event of the button.

CurrentDb.Execute "DELETE FROM MyTable WHERE [ReviewerID]=" &
Me!MyComboBox.Value
Me.Requery

Jack Cannon

As the subject states, I am trying to delete a record using a combobox and a
command button.

I have set up the combobox to allow the user to select a pesrons name from a
table. The combobox gets ReviewerID, LastName, and FirstName.

Now I want to add a command button to delete the record that was chosen.
How do I send the data from the combobox to the button and then what is
needed for the button to delete the record once it is clicked?

Thanks for the help!