delete records based on a combo box

[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

 

[tina]

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

 

[Marshall Barton]

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?

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

 

[Douglas J. Steele]

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