Solved

VBA to delete selected item in list box

Posted on 2014-07-22
4
1,013 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 40

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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