Updating Multiple Items in a listbox

[wickidwe]

I have posted this on another forum with no luck.
In my form I have the listFunctions list box set to Multi Select "Extended"
in the following code contains a line to execute a query based on the selected items in a listbox. but for some reason instead of only changing the selected items it is changing all items in the listbox.
the funny thing is I stepped through the code and it is looping the correct number of times based on the amount selected but is still changing all.

eg.
if I select 3 items from the list, it loops through the execute 3 times.
but the total 6 items will change.

in my post to the other forum I only input the code I thought was giving me the problem but I will put in the whole thing to see if anyone can shine light on the subject.

Private Sub cmdEdit_Click()
 Dim varItm As Variant
 Dim sSQL As String
 Dim ssSQL As String
 
 ssSQL = "UPDATE tblRelief_Allot SET tblRelief_Allot.GrRating = '" & Me.txtRating & "' " & _
        "WHERE tblRelief_Allot.EmpId='" & Me.listFunctions.Column(3, varItm) & "' " & _
        "AND tblRelief_Allot.ReliefCode=" & Me.cboFunction.Column(0) & ";"
 
 sSQL = "UPDATE tblRelief_Allot SET tblRelief_Allot.GrRating = '" & Me.txtRating & "' " & _
            "WHERE tblRelief_Allot.EmpId='" & Me.cboSearchName.Column(0) & "' " & _
            "AND tblRelief_Allot.ReliefCode=" & Me.listFunctions.Column(4, varItm) & ";" 'to ammend remove , varItem
 If Not (Me.listFunctions.Recordset.EOF And Me.listFunctions.Recordset.BOF) Then
     If Me.txtRating = "" Then
    MsgBox "Please Input a Rating 1-5", vbExclamation
    Exit Sub
    Else
        If Me.txtRating > 5 Then
            MsgBox "Please Input a Rating 1-5", vbExclamation
        Exit Sub
        Else
             If Me.cboSearchName = "" Then
             For Each varItm In Me.listFunctions.ItemsSelected ' Selecteditem
             
                    CurrentDb.Execute (ssSQL)
             Next
                 Me.txtRating = ""
                 Me.cmdAdd.Enabled = False
                Me.cmdEdit.Enabled = False
                Me.cmdDelete.Enabled = False
                Me.listFunctions.Requery
            Else
          For Each varItm In Me.listFunctions.ItemsSelected ' Selecteditem
          Stop
                CurrentDb.Execute (sSQL)
                
                Next
                Me.txtRating = ""
                Me.cboFunction = ""
                Me.cmdAdd.Enabled = False
                Me.cmdEdit.Enabled = False
                Me.cmdDelete.Enabled = False
                Me.listFunctions.Requery
            End If
        End If
    End If
End If
End Sub

Please Help

 

[pbaldy]

For starters, the SQL string has to be built within the loop, else varItm has no value. You can use this to examine the finished string once you've moved it:

http://www.baldyweb.com/ImmediateWindow.htm