I want to reassign salesperson associated with customers. I have an unbound form with two cboBoxes called cboFrom and cboTo. When cboFrom is selected it displays records associated with this salesperson in a multiselect listBox named lstDisplay. When I click a cmdButton I use the codes below to reassing to the salesperson selected in cboTo.

If I select a record or some records from lstDisplay all the records are being assigned to the salesperson in cboTo instead of only the ones I select from the multiselect listbox. What am I doing incorrectly.

Dim intCount As Variant

For Each intCount In lstDisplay.ItemsSelected

strSQL = "UPDATE tblAfterSoldFollowup SET SalespersonID = " & cboAssignTo & _
" WHERE SalespersonID = " & lstDisplay.ItemData(intCount)

MsgBox lstDisplay.ItemData(intCount)
CurrentProject.Connection.Execute strSQL

Next intCount
lstDisplay.Requery

I would assume that the table where you are reassigning the salesman is keyed with its own primary key, and this is what should be selected in the multi-select list box.

Your SQL string is selecting all the records for a particular salesman to be updated to the value in the AssignTo combo.

I created a small test db with a salesman table whose primary key is used to indicate the assigned salesperson in the FollowUp table. The listbox has its source set to a select query with a criteria of "SalesmanID = [cboFrom]" and the bound column is the primary key of the FollowUp table.

Your strSQL should have a criterion section of "WHERE FollowUpID = " & lstDisplay.ItemData(intCount)

Regards

Rod