delete records based on a combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a very simple form, based on a table ("Teachers") with one field
("Teacher_Name").

On the form is a combo box based on the same table and field (i.e. a
dropdown listing of all the teacher names), sorted in ascending order.

I also have a button on the form to delete a teacher name (record) with the
following on click event coding:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

It looks like I got that from the command button wizard.

Anyway, it appears that regardless of what name I have select in the combo
box, if I then hit the "delete" button it will delete the top teacher name on
the list, not the name appearing in the combo box on the form.

How can I pick a teacher name from a combo box, then hit a button to delete
that teacher name?

Thanks in advance.

Jerry
 
try the following code in the command button's Click event procedure, as

CurrentDb.Execute "DELETE * FROM Teachers " _
& "WHERE Teacher_Name = '" & Me!ComboboxName, _
dbFailOnError
Me!ComboboxName.Requery

replace ComboboxName with the correct name of the combo box control on the
form, of course.

hth
 
JWCrosby said:
I have a very simple form, based on a table ("Teachers") with one field
("Teacher_Name").

On the form is a combo box based on the same table and field (i.e. a
dropdown listing of all the teacher names), sorted in ascending order.

I also have a button on the form to delete a teacher name (record) with the
following on click event coding:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

It looks like I got that from the command button wizard.

Anyway, it appears that regardless of what name I have select in the combo
box, if I then hit the "delete" button it will delete the top teacher name on
the list, not the name appearing in the combo box on the form.

How can I pick a teacher name from a combo box, then hit a button to delete
that teacher name?
Click to expand...


That button wizard code is deleting the form's current
record, not the record specified by the combo box. You need
to run a Delete query to delete a different record.

Dim db As Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "DELETE * FROM table " _
& "WHERE teacher = """ & cboTeacher & """"
db.Execute strSQL, dbFailOnError
Set db = Nothing
 
Slight typo: you're missing the closing quote. As well, since we're dealing
with names, it's entirely possible that the name may include an apostrophe
(O'Reilly). Better would probably be:

CurrentDb.Execute "DELETE * FROM Teachers " _
& "WHERE Teacher_Name = """ & Me!ComboboxName & """", _
dbFailOnError

or

CurrentDb.Execute "DELETE * FROM Teachers " _
& "WHERE Teacher_Name = " & Chr(34) & Me!ComboboxName & Chr(34), _
dbFailOnError
 
Back
Top