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

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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) & ")"
 
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!!
 
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.
 
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) & ")"
 
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) & ")"
 
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....
 
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
 
Back
Top