Table field that contains a list.

[Onion]

This has to be simple, but I'm forced to admit that I'm a novice who can't
figure it out.

I have a listbox in a form that allows multiple selections. That works fine.

The problem: I can't figure out how to store the data. I want to store the
items chosen from the listbox in a field for the current record. That means
the table field must store the variable number of text items selected from
the listbox by the user.

Perhaps I'm going about it in the wrong way - I'm open to any suggestions.

Be gentle.

[Douglas J. Steele]

What you're trying to do is actually a violation of database normalization
principles, one of which states that each field should contain a single
value.

For this reason, Access will not let you bind a field to a multiselect list
box: only to one that allows single selections. If there's a legitimate
reason for storing the multiple values, you'll have to put code in the
listbox's AfterUpdate event to loop through the list and concatenate all of
the selected values, and then get that concatenated list into the table's
field. (A common approach would be to have a hidden text box that's bound to
the field, and set the hidden text box's value in your listbox's AfterUpdate
event)

To concatenate the fields, use something like:

Dim varItem As Variant
Dim strValues As String

strValues = vbNullString
For Each varItem In Me!MyListBox.It emsSelected
strValues = strValues & Me!MyListBox.It emData(varItem) & ", "
Next varItem

'remove the ", " from the end
If Len(strValues) > 0 Then
strValues=Left$ (strValues,len( strValues)-2))
End If
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Onion" <on***@pmosoft. com> wrote in message
news:gfsWd.6151 4$uc.7949@trndd c08...

This has to be simple, but I'm forced to admit that I'm a novice who can't
figure it out.

I have a listbox in a form that allows multiple selections. That works
fine.

The problem: I can't figure out how to store the data. I want to store the
items chosen from the listbox in a field for the current record. That
means the table field must store the variable number of text items
selected from the listbox by the user.

Perhaps I'm going about it in the wrong way - I'm open to any suggestions.

Be gentle.

[pietlinden]

I would be inclined to use a main form/subform for this. Then set the
subform to use a combobox. That way, I can look for/count/manipulate
information easily. If you loop through the selected items in the
listbox and write them to a single field, you're denormalizing, and
that could bite you later on. Say for example your listbox contains
ice cream flavors, and you write the result to a text field. Okay, how
many people ordered peach? How many people ordered raspberry? How
many ordered both? Not possible unless you do something to parse out
the data and then you can analyze it. And do you *really* feel like
writing a parser when you shouldn't have to?

What I'm getting at (hopefully nicely) is that going forward with what
you propose is a bad idea. It's just not flexible enough, and won't
serve anything more than your most immediate purposes. You won't be
able to easily query or summarize your results if you store everything
in the same field. And if you can't sort or summarize your data, why
use a database at all?

[Onion]

Thank you. That's the feedback I was looking for - it helps me understand
that my approach was wrong.

I guess to preserve the single field to single value principle, I should
store the multiple listbox selections in another table B, each in a seperate
record with a second field that contains the record ID in table A that each
selection corresponds to.

Sound right?

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:ZP******** ************@ro gers.com...

What you're trying to do is actually a violation of database normalization
principles, one of which states that each field should contain a single
value.

For this reason, Access will not let you bind a field to a multiselect
list box: only to one that allows single selections. If there's a
legitimate reason for storing the multiple values, you'll have to put code
in the listbox's AfterUpdate event to loop through the list and
concatenate all of the selected values, and then get that concatenated
list into the table's field. (A common approach would be to have a hidden
text box that's bound to the field, and set the hidden text box's value in
your listbox's AfterUpdate event)

To concatenate the fields, use something like:

Dim varItem As Variant
Dim strValues As String

strValues = vbNullString
For Each varItem In Me!MyListBox.It emsSelected
strValues = strValues & Me!MyListBox.It emData(varItem) & ", "
Next varItem

'remove the ", " from the end
If Len(strValues) > 0 Then
strValues=Left$ (strValues,len( strValues)-2))
End If
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Onion" <on***@pmosoft. com> wrote in message
news:gfsWd.6151 4$uc.7949@trndd c08...

This has to be simple, but I'm forced to admit that I'm a novice who
can't figure it out.

I have a listbox in a form that allows multiple selections. That works
fine.

The problem: I can't figure out how to store the data. I want to store
the items chosen from the listbox in a field for the current record. That
means the table field must store the variable number of text items
selected from the listbox by the user.

Perhaps I'm going about it in the wrong way - I'm open to any
suggestions.

Be gentle.

[Douglas J. Steele]

Yup, that's how it should be done. (And as Piet Linden pointed out, a
form/subform setup is the usual interface for this)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Onion" <on***@pmosoft. com> wrote in message
news:UFvWd.8001 2$wc.18809@trnd dc07...

Thank you. That's the feedback I was looking for - it helps me understand
that my approach was wrong.

I guess to preserve the single field to single value principle, I should
store the multiple listbox selections in another table B, each in a
seperate record with a second field that contains the record ID in table A
that each selection corresponds to.

Sound right?

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:ZP******** ************@ro gers.com...

What you're trying to do is actually a violation of database
normalization principles, one of which states that each field should
contain a single value.

For this reason, Access will not let you bind a field to a multiselect
list box: only to one that allows single selections. If there's a
legitimate reason for storing the multiple values, you'll have to put
code in the listbox's AfterUpdate event to loop through the list and
concatenate all of the selected values, and then get that concatenated
list into the table's field. (A common approach would be to have a hidden
text box that's bound to the field, and set the hidden text box's value
in your listbox's AfterUpdate event)

To concatenate the fields, use something like:

Dim varItem As Variant
Dim strValues As String

strValues = vbNullString
For Each varItem In Me!MyListBox.It emsSelected
strValues = strValues & Me!MyListBox.It emData(varItem) & ", "
Next varItem

'remove the ", " from the end
If Len(strValues) > 0 Then
strValues=Left$ (strValues,len( strValues)-2))
End If
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Onion" <on***@pmosoft. com> wrote in message
news:gfsWd.6151 4$uc.7949@trndd c08...

This has to be simple, but I'm forced to admit that I'm a novice who
can't figure it out.

I have a listbox in a form that allows multiple selections. That works
fine.

The problem: I can't figure out how to store the data. I want to store
the items chosen from the listbox in a field for the current record.
That means the table field must store the variable number of text items
selected from the listbox by the user.

Perhaps I'm going about it in the wrong way - I'm open to any
suggestions.

Be gentle.