Here's a challenging Q... add multiple items to single table cell?

[Woodman650]

Haha, so here's the question...
Let's say I've got a group of 50 individuals in my database. I want to create a form that outlines company projects. I'd like to have a combobox that I can begin to type a name and if it exists it fills automatically... no problem there. But then, here is the kicker, I want to select that name, hit an "add" button, and repeat the process for as many people as I need to. Everytime I add an individual, their name is entered into a single table cell, seperated by a comma.

Any ideas? ;D

 

[adalger]

My idea is, why do you want to do this? It sounds klunky and likely to cause many future problems.

 

[Woodman650]

well, it's so I don't have to type in all the names, over and over again. and so that I associate more than one person with a task, while storing all the names in a single cell (for the sake of organization). I'm sure it's by no means easy to do, but I think it could be very useful for the reasons stated above.

 

[Woodman650]

i mean... is there an easier way to add mutiple values or something of the sort?

 

[PHV]

Everytime I add an individual, their name is entered into a single table cell, seperated by a comma
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

why wouldn't you want to have a table with a single record for each person that you could then concatenate into a single field for reporting purpose.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Woodman650]

I didn't think about that lespaul... but I am still not sure how to add multiple individuals to a table from a form, even to different cells. That's the real question I've run into.

 

[Woodman650]

thanks for the link, btw PHV. =D I will read this tonight

 

[adalger]

The basic idea is, every time you hit the "Add" button, it enters a record into the table and forces the combo box to requery the list (excluding already-selected individuals). There are three distinct tasks here: inserting a record into a table, building the correct list for the combo box, and forcing the combo box to update the list.

HTH

 

[lespaul]

Everytime I add an individual, their name is entered into a single table cell, seperated by a comma.

every time you add an individual add a record to the table. When you need to report or output all the people on a project, you return a SINGLE comma separated list. There are a few FAQs that show how to return a single concatentated list from a one to many or many to many relationship.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[SeeThru]

Public Function ReturnList(JobID as variant) as string

dim rst as recordset
dim strSQL as string
dim strTemp as string

if isnull(jobid) then
 Returnlist=""
 exit function
end if

strSQL="Select * from Namelist WHERE JobID=" & jobID
set currentdb.openrecordset(strsql)

do while rst.eof=false
 strTemp=strTemp & ", " & rst!WorkerName
 rst.movenext
loop

'remove leading ,
ReturnList=mid(strtemp,3)

end function

This is not tested code - just typed it here. It takes an argument for the main record, looks up a table of related record, and returns a comma separated list giving info from all related records.

I use it alot for creating quick report where there are subforms and subtables, but I dont want to build a full subreport.