How do I create a list box on a form that selects 5 people?

[mdandridge]

I am creating a form and one of the options that I need to create is a
List/Combo Box where I can select up to 5 people that I have assigned a task
for one project. I do not want to create 5 different fields that say
"assigned to." I would like to create one field that says assigned to, which
will show that I have selected 5 individuals for the task. How do I go about
doing this?

 

[Larry]

I am creating a form and one of the options that I need to create is a
List/Combo Box where I can select up to 5 people that I have assigned a task
for one project. I do not want to create 5 different fields that say
"assigned to." I would like to create one field that says assigned to, which
will show that I have selected 5 individuals for the task. How do I go about
doing this?

On your Listbox set the Multi Select property to either Simple or
Extended.

Then loop through the listbox to get the items that were selected to
add them to your database.

Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm

 

[John W. Vinson]

I am creating a form and one of the options that I need to create is a
List/Combo Box where I can select up to 5 people that I have assigned a task
for one project. I do not want to create 5 different fields that say
"assigned to." I would like to create one field that says assigned to, which
will show that I have selected 5 individuals for the task. How do I go about
doing this?

I'd suggest correctly normalizing your tables, and using a Subform rather than
a listbox (or perhaps in addition to a listbox). You need three tables:
Projects, People, and Assignments. Assignments would have fields for the
ProjectID, the PersonID, and any needed fields pertaining to one person's
assignment to one task - e.g. date assigned, role (team leader? flunky?), date
completed, satisfactory/unsatisfactory, comments, etc.

If you base a form on Projects, and a subform on Assignments, you can put a
combo box on Assignments bound to PersonID to select a person to be assigned
to the project. Put a textbox in the subform Footer with a control source

=Count(*)

to display how many people have been assigned to this task.

John W. Vinson [MVP]

 

[Scott McDaniel]

I am creating a form and one of the options that I need to create is a
List/Combo Box where I can select up to 5 people that I have assigned a task
for one project. I do not want to create 5 different fields that say
"assigned to." I would like to create one field that says assigned to, which
will show that I have selected 5 individuals for the task. How do I go about
doing this?

Unless you want to include 5 combos, you'll have to use a listbox; you can only make one selection in a Combo.

You shouldn't create 5 different fields in your table, but instead you should create a separate table to hold the "join"
info between that table and the 5 people who should be associated with a record from that table. Then, in the Form's
Current event, you'd open the "join" table filtered for the current record then loop through those records and highlight
the corresponding rows in your listbox.

I don't know your table structure, or how your "people" table is structured, but assuming you have a table which stores
People information, and that it has a Primary Key field that is an autonumber:

'/build a table to house the relationship between your table and People
tJoins
-----------------
lPersonID (holds the value of the person you select)
lRecordID (holds the value of the Record with which this person is associated)

Set your listbox to have at least two columns; one to hold the ID of the person, the other to hold the Name. Now do
somthing like this in the form's Current event

Dim rst As DAO.Recordset'
Dim I as Integer

Set rst = Currentdb.OpenREcordset("SELECT * FROM tJoins WHERE lRecordID=" & Me.YourRecordIDField

'/first clear all choices
For i = 0 to Me.YourListbox.ListCount - 1
Me.yourListBox.Selected(i) = False
Next i
'/now highlight your selections
Do Until rst.EOF
'/loop through the listbox and select the item which matches your stored value
For i = 0 to Me.YourListbox.ListCount - 1
If Me.YourListbox.Column(0,i) = rst("lPersonID") Then
Me.YourLisbox.Selected(i) = True
End If
Next i
rst.MoveNext
Next

Again, I don't know your table structure, but to save your choices, do this:

'/first remove all existing chioces for the current record
Currentdb.Execute "DELETE * FROM tJoins WHERE lRecordID=" & ME.YourRecordIDField
'/now add the new choices
Dim var As Variant
For Each var in Me.YourListBox.ItemsSelected
Currentdb.Execute "INSERT INTO tJoins(lPersonID,lRecordID) VALUES(" & var(0,i) & "," & Me.YourRecordIDField & ")"
Next var

This will insert any row in the ItemsSelected collection of the listbox into your tJoins table. Typically you'd only do
this if the user has changed anything in the listbox (you could set a form level variable that would be set to True in
the Listbox's Change event, perhaps, or you could just do this anytime the record is dirtied, perhaps in the form's
AfterUpdate event).

Note that you will obviously have to make changes to this code to reflect the table/columm/form/control names in your
project.





Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com