Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

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?

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

Open in new window


Thank you!
0
Megin
Asked:
Megin
  • 2
1 Solution
 
als315Commented:
Try this
 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)
 

Open in new window

If any of parameters is string, double quotes before and after it:
chr(34) &  ctl.Column(2, i) & chr(34)
0
 
MeginAuthor Commented:
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:

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

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try changing your strsql with this


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)
0
 
MeginAuthor Commented:
That worked.

Thank you!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now