Help with Multiselect List Box

M

Michelle

I have the following code that allows the user to select items in a list box
and show them in a text box. However, if the user comes back to a record and
wants to add additional names to the text box, it clears out the previous
selection.

Here is my code:

Dim varItem As Variant
Dim strList As String

With Me.Employee
If .MultiSelect = 0 Then
Me.Employee = .Value
Else

For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & vbCrLf
.Selected(varItem) = False 'unselect listbox

Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.Employee = strList
End If

End With

Can anyone help with the code of adding additional employees to the text box
while keeping any original employees previously selected. Thanks.
 
K

Klatuu

I assume Me.Employee is the text box, but assuming it is:
Me.Employee = Me.Employee & ", " & strList

Not sure why you need to put multiple names in one text box, but this will
do it.
 
M

Michelle

Dave, I put your code in but it still cleared the previous names out of the
text box. I am curious about your comment "not sure why you need to put
multiple names in one text box" and was wondering if maybe there was a better
way to do what I want?

I have a list of employees in a table and the user needs to be able to
choose anywhere from 1 to 100 employees that could be associated with a
particular record in my database. It's a training database and we need to
see who is trained in that particular area. The user would like the list of
employees to choose from and would like to highlight each employee and show
what employees were highlighted. They would also like to be able to come
back and add additional employees to the text box. If there is a better way
to do this, I'm open to suggestions. (I didn't explain it clearly, please
let me know). Thanks again for your help.
 
K

Klatuu

Sorry, Michelle, I misread your post earlier. That was not actually the
problem. Now that I examine it a bit closer, I don't see the problem. I am
wondering if this
line:
.Selected(varItem) = False 'unselect listbox
Could be the problem. This is not an answer but a suggestion on where to
look. In some looping circumstances, when you modify the number of items in
the list, it can cause the loop control logic to become confused. In any
case, here is a trick for unselecting everything in a list box:

Me.MyListBox.ListIndex = -1
 
M

Michelle

Dave, when I put in the line

Me.MyListBox.ListIndex = -1

I get a runtime error "7777" and the message "you've listed the ListIndex
property incorrectly". I substituted "Employee" for "MyListBox"??

Anyway, I'm so confused on this multi selection list box. I also found that
if I select a lot of names, it says my field is too small. I have the field
set as memo and the field size as 255. Is there a better way to do what I
want? I've been reading about using a subform but I must be extra blonde
today because it's just not clicking with me!

My main objective is to select names from "Employees" list box (it's
actually a table called Hourly Employee Table for Quality Alert) and there
are 863 names in this table and I would like to be able to highlight from
1-863 names if needed. I want to show the selection in another text box,
combo box, form, or whatever I can, just to show who was selected for each
record. Any suggestions are appeciated. Thanks.
 
K

Klatuu

I was afraid you would blow the text box size limit with that.
A suform will not allow you to select multiple employee records directly.
You could add a check box to your subform to allow you to select them, but I
don't really understand what it is you are about. All selected items in a
list box show as seleted. Can you give me an idea of the objective, maybe
there is a more simple way to get what you need.
 
M

Michelle

I have a list box on my form that is based on a table of all employee names
(there are 863 employees right now but more will be added in the days to
come). When the user (supervisor) opens an "alert" record they need to be
able to select which employees were trained on that particular "alert".
There are 100s of alerts and each alert will have different employees who are
trained on them. I need a way for the user (supervisor) to select the
employees that are "trained" on each alert, and then need be able to see
which employees the user selected so we know who was trained on each alert
(would like to see the employees in a text box or form). I would then like
to be able to clear out the selections for the next record (or alert) in the
database.

Like I said, any suggestions are welcome.

(It's frustrating, I know what I'm trying to say, but guess I'm not
explaining very well). Thanks again for all your help.
 
K

Klatuu

With that many employees, you will never be able to see them all at once.
Now, I'm thinking subform. Can you tell me how you identify which employees
have been trained on which alerts?
 
M

Michelle

Right now the supervisor checks (or selects) the employees based on his
knowledge. The employees are in a list box called "Employee" based on a
table called "Hourly Employee Table for Quality Alert".
 

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