deleting record via combo box selection

  • Thread starter Thread starter wheat
  • Start date Start date
W

wheat

I'm trying to delete a record from my table based on the current
selected item in a combo box. The user should be able to make a
selection and press a button labled "delete record" to delete the
record. So far, my delete button just deletes the first record in the
combo box (it will do this each time until there are no remaining
records). I'm assuming the bookmark method will be of help here, but
I haven't had any luck with it. Here's the code for the subroutine
called by the button on click (the combo box is named
"existing_contact":

Private Sub delete_contact_button_Click()
' ----------------------------------------
' delete a record from the 'contacts' table via the combobox
' ----------------------------------------

Dim db As DAO.Database
Dim rs As DAO.Recordset

' use the 'contacts' table of the current database
Set db = CurrentDb()
Set rs = db.OpenRecordset("contacts")

With rs
' check for nulls
existing_contact.SetFocus
If existing_contact.Text = "" Then
MsgBox ("Please select a record to delete")
End
Else
.Delete
.MoveNext
End If

' refresh the combo box listing
existing_contact.Requery

End With

rs.Close

Exit_add_contact_Click:
Exit Sub

Err_add_contact_Click:
MsgBox Err.Description
Resume Exit_add_contact_Click

End Sub
 
Here's an alternate way of doing what you want!

Create a continuous form based on the same rowsource as your combobox. This form
will list the same records as your combobox. Put the following code in the
double click event of one of the fields:
DoCmd.SetWarnings False
DoCmd.RunCommand AcDeleteRecord
DoCmd.SetWarnings True

What ever record you double click on (in the field with the code) will be
deleted.
 
PC Datasheet said:
Here's an alternate way of doing what you want!

Create a continuous form based on the same rowsource as your combobox.
This form
will list the same records as your combobox. Put the following code in the
double click event of one of the fields:
DoCmd.SetWarnings False
DoCmd.RunCommand AcDeleteRecord
DoCmd.SetWarnings True

What ever record you double click on (in the field with the code) will be
deleted.
Click to expand...

That's a cool trick, and I'll file that away for future use. But, for
this project, it would be must better if I could use the combo box to
select the record to be deleted (there are two buttons beneath the
combo box: one selects the record for editing, the other selects it
for deletion). Selecting for editing is working fine, not so with
selecting for deletion.

Do you know how to do that as well?

James
 
Back
Top