Megin
asked on
VBA to delete selected item in list box
I want to delete items selected from a list box in my form.
I have a piece of code that worked well in another form and tried to make it work here, but it isn't working.
I get the following error message "You can't assign a value to this object"
Am I missing something really simple?
Thank you!
I have a piece of code that worked well in another form and tried to make it work here, but it isn't working.
I get the following error message "You can't assign a value to this object"
Am I missing something really simple?
Private Sub btnDelete_Click()
Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strsql As String
Dim i As Variant
Set frm = Forms("frm_SubTaskOrders")
Set ctl = frm![lstSubTaskOrders]
Set db = CurrentDb
For Each i In ctl.ItemsSelected
strsql = "DELETE FROM SubTaskOrders WHERE"
[STOid] = " & ctl.Column(0, i)" & " AND [STONo] = " & ctl.Column(1, i) & " AND [STOName] = " & ctl.Column(2, i) & " And [Toid] = " & ctl.Column(3, i)
db.Execute strsql, dbFailOnError
Next i
ctl.Requery
CurrentDb.Execute strsql, dbFailOnError
End Sub
Thank you!
ASKER
Now I am getting a different error:
Syntax error (missing operator) in query expression '[STOid] = 9 AND [STONo] = 2 AND [STOName] = Tech Coordination AND [TOid] = 1 AND [OrderNumber]=2'
It appears to be pulling information from the table in this error.
Also, I forgot that I had one more field I needed to delete information from in this table (OrderNumber), so I added it to the list box and to the code. So this is what I have:
Syntax error (missing operator) in query expression '[STOid] = 9 AND [STONo] = 2 AND [STOName] = Tech Coordination AND [TOid] = 1 AND [OrderNumber]=2'
It appears to be pulling information from the table in this error.
Also, I forgot that I had one more field I needed to delete information from in this table (OrderNumber), so I added it to the list box and to the code. So this is what I have:
Private Sub btnDelete_Click()
Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strsql As String
Dim i As Variant
Set frm = Forms("frm_SubTaskOrders")
Set ctl = frm![lstSubTaskOrders]
Set db = CurrentDb
For Each i In ctl.ItemsSelected
strsql = "DELETE FROM SubTaskOrders WHERE " & _
"[STOid] = " & ctl.Column(0, i) & " AND [STONo] = " & ctl.Column(1, i) & " AND [STOName] = " & ctl.Column(2, i) & " And [Toid] = " & ctl.Column(3, i) & " And [OrderNumber] = " & ctl.Column(4, i)
db.Execute strsql, dbFailOnError
Next i
ctl.Requery
CurrentDb.Execute strsql, dbFailOnError
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked.
Thank you!
Thank you!
Open in new window
If any of parameters is string, double quotes before and after it:chr(34) & ctl.Column(2, i) & chr(34)