Problem with Items Selected from List Box

[kc-mass]

Hi,
I have a form with a multi select list box containing client names. The
form also has a combo box to pick out the employee that the selected clients
will be assigned to. Also on the form is a command button with an on click
event that when invoked is intended to record in a table both the employeeID
and the clientID If I make three selections in the list box it will record
three records in the table. The problem is that each of the three records
will have the same clientID rather than the three distinct IDs. It always
records the ID of the last client selected.

Any help appreciated. Code is below

Thanks

Private Sub cbtStartAssignment_Click()
Dim varItem As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAssignments")
With Me!lstCustomers
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
rs.AddNew
rs!CustomerID = .Column(0)
rs!EmployeeID = Me!txtEmployeeID
rs!startdate = Me!txtEffectiveDate
rs.Update
End If
Next varItem
End With
Me!lstAssignments.Requery
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[Douglas J. Steele]

Change

rs!CustomerID = .Column(0)

to

rs!CustomerID = .Column(0, varItem)

Incidentally, there's no need for the check If Not IsNull(varItem) Then:
varItem is guaranteed to be a valid value.

 

[kc-mass]

Thanks Doug - worked great.

Regards

Kevin

Change

rs!CustomerID = .Column(0)

to

rs!CustomerID = .Column(0, varItem)

Incidentally, there's no need for the check If Not IsNull(varItem) Then:
varItem is guaranteed to be a valid value.