Deleting a selected record

[steve711]

Hello All,

I am having an issue with this code:

strSQL = "DELETE * FROM [tbl_Department] WHERE " & _
"[Department] = """ & Me.lst_Dept & """;"

DoCmd.RunSQL strSQL
Me.Department = ""
Me.lst_Dept.Requery


I am trying to let the user delete a department from a listbox. They click on the department to delete then click DELETE and the magic should happen. Well it looks like it is happening, however, when I open the form again there is a blank entry in the list and of course in the table.

1st why is that happening and second how do I fix it from happening?

 

[x0reset]

Are you using a primary key in tbl_Department? If so, is a text field?

 

[steve711]

Are you using a primary key in tbl_Department? If so, is a text field?

Yes, and there is only one field in tbl_Department and it is a text field.

 

[x0reset]

I assume Me.Department is bound to the department field. The following code is actually adding a null vallue to the table, which shows up as a blank entry in the list box and the table.

'Sets the value of the current record to "":
Me.Department = ""
'Goes to a new record, adding the "" entry to the table:
Me.lst_Dept.Requery

Try removing the Me.Department = "" and see what happens.

 

[steve711]

Ok I found the problem now.

Actually 2 problems.

1st....Was the one you ID'd
2nd....The list box was also bound to tbl_Department and that was adding into the table when I closed the form each time.

Unbound it and all works as hoped. Thanks for the inspiration.