Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / June 2007

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
FiremanJas - 01 Apr 2007 15:26 GMT
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
Daniel - 01 Apr 2007 16:10 GMT
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

Signature

Hope this helps,

Daniel P

> I need to be able to add multiple names to a field on a form from a list box.  
>
[quoted text clipped - 5 lines]
>
> Thanks
b_a_redman - 29 Jun 2007 10:32 GMT
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

> You can loop through the selected items and add them to the control.  Try
> something like what do i put in place of ControlName, ctl. Can you clearly identify what must be changed to match my form
[quoted text clipped - 16 lines]
> >
> > Thanks
Ken Sheridan - 29 Jun 2007 16:30 GMT
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

> 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
[quoted text clipped - 19 lines]
> > >
> > > Thanks
KARL DEWEY - 02 Apr 2007 04:46 GMT
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.
Signature

KARL DEWEY
Build a little - Test a little

> I need to be able to add multiple names to a field on a form from a list box.  
>
[quoted text clipped - 5 lines]
>
> Thanks
Ken Sheridan - 02 Apr 2007 17:12 GMT
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

> I need to be able to add multiple names to a field on a form from a list box.  
>
[quoted text clipped - 5 lines]
>
> Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.