For Each..Next - What's wrong with this code?

[scroce]

This code works except that when there are multiple choices selected in listbox lstChosenOnes. The code will delete the first choice, but not any of the subsequent ones. When I watched the variables change, varItem remains on the user's original first choice when it should be iterating down to whatever the next choice the user has chosen in the list box.

Can anyone help me? Thank you. See code below:


Private Sub cmdDeleteChosenOnes_Click()

Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim dbCurrent As Database
Dim strSQL As String
Dim strDeleteQuery As String


Set frm = Forms!frmDeleteChosenOnes
Set ctl = frm!lstChosenOnes
Set dbCurrent = CurrentDb


For Each varItem In ctl.ItemsSelected

varItem = ctl.Column(0)
strDeleteQuery = "Delete * FROM tblChosenOnes
WHERE ChosenOnes = '" & varItem & "';"

DoCmd.RunSQL (strDeleteQuery)

Next varItem

End Sub

 

[MichaelRed]

I think you need to make each [varItem] the "selected" item in the listbox, and then use the listbox 'selecteditem' as your criteria.

I haven't used this is a while, so memory may be fuzzy/wrong, but the criteria in the where clause (obviously) needs to be changed to point to the (proper) collection.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Durkin]

I think you need to use
ctl.itemdata(varItem)
or if you have multiple columns in your list box
ctl.Column(i, varItem)
(where i is the column number you want to use)
instead of just
varitem
in your SQL statement. Durkin
alandurkin@bigpond.com

 

[chell]

Hello;

Here is a slightly rewritten version of your code which I think will work: (some word wrapping has occurred during the pasting of the code)

For Each varItem In ctl.ItemsSelected
'Notes:
' >VarItem is an integer used as an index which refers to a selected item.
' It is not the value of the selected item.
' >The following assumes that ChosenOnes is the bound column of the list box.
' If it is not then use ctl.Column(x,varItem) where x is the number of the correct column.
' Zero is the first column.
' >Apostrophe in the WHERE clause is only required if ChosenOnes is of type TEXT.

strdeletequery = "Delete * FROM tblChosenOnes WHERE ChosenOnes = '" & ctl.ItemData(varItem) & "';"

'You should look at "Call CurrentDb.Executem (strDeleteQuery)" since this is VBA.

Call DoCmd.RunSQL(strdeletequery)
Next varItem

'Requery the list box to see the changes.
'One problem with your code sample is that changes to the table
'underlying the list box do not appear until the list box is requeried.
'Of course, requerying the list box clears the items the user selected.

ctl.Requery


And here is a slightly better version:
'A final note: It might be better to look at making a compound query for reasons of performance.
'It's usually better to run a query once than multiple times.
If ctl.ItemsSelected.Count > 0 Then
strdeletequery = "(ChosenOnes = '" & ctl.ItemData(0) & "') "

For varItem = 2 To ctl.ItemsSelected.Count
strdeletequery = "OR (ChosenOnes = '" & ctl.ItemData(varItem - 1) & "') "
Next

strdeletequery = "Delete * FROM tblChosenOnes WHERE (" & strdeletequery & ""

Call CurrentDb.Execute(strdeletequery)
End If
ctl.Requery


Hopefully this will be of help;
Chell