Full Version: Delete Tech from List Box
UtterAccess Forums > Microsoft® Access > Access Forms
big0
I created a form to delete users from my Db. On my command button, I'm having some issues with my delete statment. I keep running into a syntex error. I have attached my down size version of my db. Please review and give me some advise on how to correct this.
Thanks
big0
kbrewster
Your SQL statement is wrong...
You have:
CODE
"DELETE * Technicians WHERE ='" & myTech & "'));"

And it should be:
CODE
"DELETE * FROM Technicians WHERE='" & myTech & "';"

Also, I am not a big fan of permanently deleting people out of DB's. If you are tracking the technicians in other tables, as soon as you delete the technician, you will not have any reference to them. Your best best is it have a TechStatus in your Technicians table and just make them inactive rather than deleting them.
kbrewster
Also you should have the TechnicianID as column zero. What if they have the same last name? Then it will delete EVERYONE with that last name. I am uploading an example. All I did was add the PK to the list box and set the column width to zero, and then I edited the SQL code.
kbrewster
Sorry, sent the wrong one - here is the correct one.
Clippit
Kristen. I agree the list box should have TechnicianID as its bound column and the delete should use that, rather than name to find records. I think the final SQL will be more like this:

dbs.Execute "DELETE * FROM Technicians WHERE TechnicianID =" & myTech & ";"


Edt:: and now that I see your second attachment I see you got exactly the same place I did.....
Edited by: Clippit on Fri Sep 5 12:20:41 EDT 2008.
big0
Thanks for assitance.
o you saying I should include the PK in my list box?
Onoticed the change in my SQL. myTech = Me.txtUser.Column(0)
Why .Column(0) ?
Dim myTech As Long
Dim dbs As Database, ctlList As Control
On Error GoTo myTechError
myTech = Me.txtUser.Column(0)
Set dbs = CurrentDb
dbs.Execute "DELETE * FROM Technicians WHERE TechnicianID =" & myTech & ";"
kbrewster
Well you do not need .Column(0) since it defaults to the first column. Columns start numbering at 0.
Clippit
You use .Column( some number ) to get at the data from the list box in a different column than the bound columnc. In this case I'd suggest you make TechnicianID the bound column and then you can just do
myTech = Me.txtUser
(Of course it would be good form to change the list box name to something more meaningful- rather than txtUser perhaps lstTechnicians.)
Add TechnicianID to the list box but don't show it- make its column width 0.
big0
Thanks for sharing. I will do ask you suggested.
Thanks kbrewster - Utter Access Ruler
Thanks Clippit - Utter Access VIP
Its good to know folks willing to help others out. Its much appriciated
big0
Clippit
That's what UA is all about!
uarulez2.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.