Problem with Items Selected from List Box


K

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
 
Ad

Advertisements

D

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.
 
Ad

Advertisements

K

kc-mass

Thanks Doug - worked great.

Regards

Kevin
Douglas J. Steele said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top