Multi Select changing all withing listbox instead of the selected items

[WickidWe]

I have the following code attached to an edit button for a listbox

Dim varItm As Variant
 Dim sSQL As String
 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

I have tried multiselect simple and also extended


instead of editing the items I have selected it is changing all the items that are in the listbox

any ideas?

[June7]

You want to run an UPDATE against the records selected in listbox? Need loop code that reads each item of the list box and if it is selected, do something with it.

1. within the loop, run the UPDATE action on selected item

2. within the loop, construct a WHERE clause variable then use the variable outside the loop in the UPDATE sql.

Code to loop through listbox, review: http://allenbrowne.com/ser-50.html

[WickidWe]

My code now looks like "Below" but it is still changing everything

Dim varItm As Variant
 Dim sSQL As String
 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) & ";" 
 For Each varItm In Me.listFunctions.ItemsSelected
        CurrentDb.Execute (sSQL)
        Next

[June7]

Debug. Refer to link at bottom of my post for guidelines. Set breakpoint and Debug.Print. Is the sSQL string construct correct?

[WickidWe]

I think the construction is correct as if I simply have "Me.listFunctions.Column(4)" instead of "Me.listFunctions.Column(4, varItm)"
the code works but only for the last item that was selected

[June7]

Right, construct the sql string and run the Execute within the loop.

[WickidWe]

Ok I Did this, I made a breakpoint at the sql statement.
the funnything is I can see it looping the same amount of times as items selected
ie. If I selected 2 items it would loop through the SQL Statement twice
and if i selected 4 items it would loop through 4 times.
but it is physically changing all?
How is this even possible?

here is my code now

 For Each varItm In Me.listFunctions.ItemsSelected ' Selecteditem
            Stop
            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, varItem) & ";" 'to ammend remove , varItem
                
                CurrentDb.Execute (sSQL)
                Next

[June7]

That is odd. Is the compiled sql statement for each loop correct?

[WickidWe]

how would I Create a "Where Variable" in the same code as code above?

[June7]

You are already constructing the WHERE clause in the code. The sql should update only the records that match the criteria. I don't understand why it does otherwise.

If you want to instead construct the multiple value string and use the IN operator, Allen Browne code shows that.

[WickidWe]

sorry Im not sure how I would do that with this code. I am a complete novice.
Could you please construct for My SQL so I can see in action?

[June7]

I am not sure it will solve the issue. Really need to understand why the existing code fails.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ItsMe]

Looks like you are updating one record, the record that equals a combo. I will guess you combo value is not changing as you loop through the selections in your listbox