I want to add multiple names to a field from a list box, how?

[Guest]

I need to be able to add multiple names to a field on a form from a list box.

I have a form that records training sessions, and I need to be able to add
multiple names of people at a session to the name field, this is selected
from a list box.

Any help would be greatly appreciated.

Thanks

 

[Guest]

You can loop through the selected items and add them to the control. Try
something like

Dim varItem As Variant
Dim strUserSel as String
Set ctl = frm!lbMultiSelectListbox
For Each varItem In ctl.ItemsSelected
strUserSel = strUserSel & ctl.ItemData(varItem)
Next varItem
Me.ControlName = strUserSel

 

[Guest]

You should not do it by putting multiple names in a single record. Have a
table of classes with a one-to-many relation to the atendees. You need a
separate record for each person so as to record test
scores/completion/attendance/etc.

 

[Guest]

You have a many-to-many relationship between the people and the sessions,
i.e. each person (employee?) can attend one or more sessions and each sessi0n
can be attended by one or more people. A many-to-many relationship is
modelled by a third table with two foreign key columns referencing the
primary keys of the two referenced tables, so you might have tables
Employees, Sessions and Attendances, the latter with foreign key columns
EmployeeID and SessionID. Together these two columns constitute the primary
key of the table.

Any other attributes of the employee's attendance at a particular session,
e.g. an assessment rating, would be represented by columns in the Attendances
table.

For data input you would use a form in single form view based on the
Sessions table and within it a subform based on the Attendances table. This
would have a combo box bound to the EmployeeID column along with controls
bound to any other columns in the table, apart form the SessionID column
which you don't need to show on the subform at all. The subform would be in
continuous form or datasheet view and linked to the parent form on the
SessionID columns by setting the LinkMasterFields and LinkChildFields
properties of the subform control (that's the control on the parent form
which houses the subform).

Assuming the Employees table has columns FirstName and LastName the
RowSource property of the combo box on the subform bound to the EmployeeID
column would be:

SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees
ORDER BY LastName, FirstName;

Other properties of the combo box would be:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, EmployeeID, column, so only the
name shows.

Ken Sheridan
Stafford, England

 

[Guest]

Im trying to do the same thing, and i am having problems doing that code. Im
confused on where i am suppose to put specific names. For example

 

[Guest]

As Karl and I have both pointed out, storing multiple values in the same
field in one row in a table is not the way to go about it. The table will
not be in First Normal Form which requires only one value to be stored at any
column position in any row in a table.

If you take a look at my first reply (currently the bottom post in this
thread) you'll find an explanation of how to do this sort of thing correctly
by basing a subform on a table which models the many-to-many relationship
between two tables.

It would be possible, using code, to insert rows into the table modelling
the many-to-many relationship by means of a multi-select list box, but a
subform is the usual means of doing it, and much easier to set up. If a list
box is used then you'd loop through the ItemsSelected collection of the list
box in the same way and execute an SQL statement in each iteration of the
loop to insert a row into the table. If the list box is also used to display
the related set of values from the table then you'd also need code in the
form's Current event procedure to examine the table and select the relevant
rows in the list box. A subform on the other hand needs no code whatsoever.

Ken Sheridan
Stafford, England