On May 3, 4:39 pm, mdandridge <mdandri...@discussions.microsoft.com>
wrote:

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

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]

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