I haven't been able to find any help for this issue on the forums already, which usually means I'm not using the right terminology, so bear with me!

I'm trying to use a form with a combo box to add more than one choice from the combo box to a field. I borrowed some code that I knew accomplished this, but it's not working for me. Every time a new choice is selected from the combo box, the field it updates changes. I want to add each separate choice, so that if i clicked on "OptionA" once and then clicked on "OptionB" the field would read "OptionA, OptionB" rather than replacing OptionA with OptionB.

Here's the code I'm using:

If IsNothing(Me!Text64) Then
Me!Text64 = Me!QuotesList
Me!QuotesSub = Me!QuotesList.Column(0)
Else
Me!Text64 = Me!Text64 & ", " & Me!QuotesList
Me!QuotesSub = Me!QuotesSub & ", " & Me!QuotesList.Column(0)
End If

Again, since I borrowed this code I don't fully understand it myself. It should be using data in Quoteslist to update QuotesSub and add each update. BTW, i don't really grasp columns, so if that's the source of my difficulty i won't be surprised.

Thanks for any help!

Whoops, should have mentioned this code is in the AfterUpdate event of a form.

Hi
Is your goal to save this data back to the table..?

Yes.

Using the fields mentioned in the code above:

"QuotesList" is a combobox and has the different options

"QuotesSub" is a text field where I want the selected options stored.

Hi
Not a good idea..
Data should not be stored like that and it would be better using a listbox where you can apply the mult-select option and store each selected item as a individual record.

So if i had 4 options in the list box, I should just create four fields, for Input1, Input2, Input3, Input4? Actually, I think you're right that this makes a lot more sense. I could use a query to make them appear in one field for a report if i needed it.

But what if I had 15 different options? I would need up to 15 different fields added to my table, wouldn't I?

thanks much for your continuing assistance.

NO! That would be a repeating group which would violatate normalization rules. What Larry said was to store each as an individual record in a child table.

Hi
Be careful of your table structure your are implying the use of "repeating groups".. not good at all.
Your structure should be like this.

tbllistboxselections
listboxid - autonumber(pk)
listboxitem - text

eg:
1- item1
2- item2
3- item3
4- item4
etc..

So if I have 30 different responses then I should have 30 child tables?

Does that mean I would have a sub form for each child table? Or build the form off of a query that combines all the child tables?

In your example, how would the selections in tbllistboxselections then be tied to the record i'm updating in the form?

So where InputID is the pk for the record i am updating with a form, listboxid 1 thru 4 are added to tbllistboxselections, but how are these listboxid's associated with InputID?


I used to think I was pretty handy in Access, but this Normalization concept is new to me. I've printed off Crystal's post on the subject, but I haven't digested it yet.

Hi
No.. a single child table to hold all your selected values and some other id field linking back to the parent/master table.
If I use my previous sample:

tblMaster
Masterid - autonumber(pk)

One to Many relationship

tblChild
listboxid - autonumber(pk)
fkMasterid - Number
listboxitem - text

eg:
1 - 1 - item1
2 - 1 - item2
3 - 1 - item3
4 - 1 - item4
5 - 2 - item1
6 - 2 - item2
7 - 2 - item3
8 - 3 - item1
9 - 3 - item2
etc..