Problem with selecting in list box and delete selected items in ta

G

Guest

Hello,
In my form, I have a text box for business line (txtSelBusLine) and a
multiple selection list box (lstAuth_Ex) for stock exchanges. List box is
pulling data from a table (Parent_Child_Auth_EX) where it stores which
business line has what exchanges.
What I want to do is user will select items they want to delete from
lstAuth_Ex, click a button and the selected items belongs to the business
line will be deleted from the Parent_Child_Auth_EX table
I have these code, and have step through them, everything seems to run fine,
but the selected items are still not deleted from the table.
Can someone please tell me what's wrong? My sql statement or the logic?

Private Sub cmdDelEx_Click()
Dim db As Database
Dim rstCur As Recordset
Dim VarSelectEx As Variant
Dim SelectEx, DelSQL As String

DelSQL = "DELETE Parent_Child_Auth_EX.Parent_Child_Name,
Parent_Child_Auth_EX.Auth_Ex " _
& "FROM Parent_Child_Auth_EX " _
& "WHERE (((Parent_Child_Auth_EX.Parent_Child_Name)='" &
Me.txtSelBusLine.Value & "') " _
& "AND ((Parent_Child_Auth_EX.Auth_Ex)='" & SelectEx & "'));"

For Each VarSelectEx In Me.lstAuth_Ex.ItemsSelected
SelectEx = Me.lstAuth_Ex.ItemData(VarSelectEx)
DoCmd.RunSQL DelSQL
Next

Me.lstAuth_Ex.Requery

End Sub
 
M

Marshall Barton

SW said:
In my form, I have a text box for business line (txtSelBusLine) and a
multiple selection list box (lstAuth_Ex) for stock exchanges. List box is
pulling data from a table (Parent_Child_Auth_EX) where it stores which
business line has what exchanges.
What I want to do is user will select items they want to delete from
lstAuth_Ex, click a button and the selected items belongs to the business
line will be deleted from the Parent_Child_Auth_EX table
I have these code, and have step through them, everything seems to run fine,
but the selected items are still not deleted from the table.
Can someone please tell me what's wrong? My sql statement or the logic?

Private Sub cmdDelEx_Click()
Dim db As Database
Dim rstCur As Recordset
Dim VarSelectEx As Variant
Dim SelectEx, DelSQL As String

DelSQL = "DELETE Parent_Child_Auth_EX.Parent_Child_Name,
Parent_Child_Auth_EX.Auth_Ex " _
& "FROM Parent_Child_Auth_EX " _
& "WHERE (((Parent_Child_Auth_EX.Parent_Child_Name)='" &
Me.txtSelBusLine.Value & "') " _
& "AND ((Parent_Child_Auth_EX.Auth_Ex)='" & SelectEx & "'));"

For Each VarSelectEx In Me.lstAuth_Ex.ItemsSelected
SelectEx = Me.lstAuth_Ex.ItemData(VarSelectEx)
DoCmd.RunSQL DelSQL
Next

Me.lstAuth_Ex.Requery

End Sub
Click to expand...


You are constructing the SQL statement before determining
the value of SelectEx.

You could move the DelSQL=... statement down to just before
the RunSQL line. Or you could construct a different
criteria amd mor the RunSQL out of the loop with this kind
of logic:

DelSQL = "DELETE *
& "FROM Parent_Child_Auth_EX " _
& "WHERE Parent_Child_Name='" & Me.txtSelBusLine _
& "' AND Auth_Ex IN ("

For Each VarSelectEx In Me.lstAuth_Ex.ItemsSelected
SelectEx = SelectEx & "," _
& Me.lstAuth_Ex.ItemData(VarSelectEx)
Next
DoCmd.RunSQL DelSQL & Mid(SelectEx, 2) & ")"
 
G

Guest

Wow Thanks Marsh, never thought of having a loop in an sql statement!
But there is some syntax error when I run the code.
For instance business line "123" I have selected "ABC" and "XYZ, Milan,
Italy" in the list box. In the For Next loop, the first SelectEx has ",ABC"
and the second SelectEx has ",ABC,XYZ, Milan, Italy"
When I excute the DoCmd.RunSQL, it gives me this err msg:
Syntax error (missing operator) in query expression 'Parent_Child_Name='123'
AND Auth_Ex IN (ABC,XYZ, Milan, Italy)'.

It must be something very minor...but I never creat sql statement like this
so I don't know how to fix it.....

Thanks again!!
 
M

Marshall Barton

SW said:
Wow Thanks Marsh, never thought of having a loop in an sql statement!
But there is some syntax error when I run the code.
For instance business line "123" I have selected "ABC" and "XYZ, Milan,
Italy" in the list box. In the For Next loop, the first SelectEx has ",ABC"
and the second SelectEx has ",ABC,XYZ, Milan, Italy"
When I excute the DoCmd.RunSQL, it gives me this err msg:
Syntax error (missing operator) in query expression 'Parent_Child_Name='123'
AND Auth_Ex IN (ABC,XYZ, Milan, Italy)'.

It must be something very minor...but I never creat sql statement like this
so I don't know how to fix it.....
Click to expand...


I didn't realize that the data field is a Text field..
Change it to put quotes around the values:

SelectEx = SelectEx & ",""" _
& Me.lstAuth_Ex.ItemData(VarSelectEx & """")

The loop is not inside of the SQL atatement! All the code
is doing is canstructing an SQL statement. If you have
further questions, please include a Copy/Paste of the code
as is at that time.
 
G

Guest

Sorry Marsh for bothering you again... here is the code now
-----------------------------------------------------------------
DelSQL = "DELETE *" _
& "FROM Parent_Child_Auth_EX " _
& "WHERE Parent_Child_Name='" & Me.txtSelBusLine _
& "' AND Auth_Ex IN ("

For Each VarSelectEx In Me.lstAuth_Ex.ItemsSelected
SelectEx = SelectEx & ",""" _
& Me.lstAuth_Ex.ItemData(VarSelectEx & """")
Next
DoCmd.RunSQL DelSQL & Mid(SelectEx, 2) & ")"
 
M

Marshall Barton

SW said:
Sorry Marsh for bothering you again... here is the code now
-----------------------------------------------------------------
DelSQL = "DELETE *" _
& "FROM Parent_Child_Auth_EX " _
& "WHERE Parent_Child_Name='" & Me.txtSelBusLine _
& "' AND Auth_Ex IN ("

For Each VarSelectEx In Me.lstAuth_Ex.ItemsSelected
SelectEx = SelectEx & ",""" _
& Me.lstAuth_Ex.ItemData(VarSelectEx & """")
Next
DoCmd.RunSQL DelSQL & Mid(SelectEx, 2) & ")"
Click to expand...


Sheesh, my syntax was totally fubar. You need to improve
your proof reading skills ;-)

Maybe this will be closer?

DelSQL = "DELETE * " _
& "FROM Parent_Child_Auth_EX " _
& "WHERE Parent_Child_Name='" & Me.txtSelBusLine _
& "' AND Auth_Ex IN ("

For Each VarSelectEx In Me.lstAuth_Ex.ItemsSelected
SelectEx = SelectEx & ",""" _
& Me.lstAuth_Ex.ItemData(VarSelectEx) & """"
Next
DoCmd.RunSQL DelSQL & Mid(SelectEx, 2) & ")"
 
G

Guest

Thanks Marsh, it is working fine now.
I took a closer look of your code and now I understand you are trying to
create an sql like this....
 
D

Douglas J. Steele

Since Delete deletes entire rows, you don't need DELETE column_name FROM
table_name.

It's either DELETE * FROM table_name, or simply DELETE FROM table_name
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List Hangs when selecting all Items in list (See Code) 1
Mutiple items selected in a list box 1
multi-select list box in sql statement issue 3
Problem with Items Selected from List Box 2
List box problem 2
Delete records from a table based on selecting in a list box 1
multi selecting items in a list box based on values from a text box 1
My Selected print in the list box does not work 4

Top