Solved

VBA to delete selected item in list box

Posted on 2014-07-22
4
878 Views
Last Modified: 2014-07-22
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
Comment
Question by:Megin
  • 2
4 Comments
 
LVL 39

Expert Comment

by:als315
ID: 40212398
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
 

Author Comment

by:Megin
ID: 40212503
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40212514
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
 

Author Closing Comment

by:Megin
ID: 40212736
That worked.

Thank you!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now