Multi-Select List Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to save the data chosen from a multi-
select list box in a single table field? For example, In
the list box, the user chooses Animal, Insect, and
Nature. The table then saves all three of these choices
in the field "Topic".

Thanks for any help!
Tara
 
Add a text box control to your form. Set its...
'ControlSource' property to 'Topic'
'Visible' property to 'No'
'Name' property to 'txtTopic'

Use the AfterUpdate() event of your existing list box to
populate it.

Private Sub MyListBox_AfterUpdate()
Dim var as Variant
Dim ctl as ListBox
Dim str as String
For Each var in ctl.ItemsSelected
str = str & ";" & ctl.ItemData(var)
Next var
Me!txtTopic = Mid(str,2)
End Sub
 
Just to tag along on Elwin's post, while what he suggested will work, I felt
obligated to mention that it's not a good idea to store multiple values in a
single field. It makes it very difficult to query the data. And, for what
it's worth, it's a violation of relational database theory.
 
I tried to use this code in my database and can am
getting an error of "runtime error 91: object variable or
with block variable not set."

any thoughts as to how i can correct this?
 
I got the same message. I'm not very familiar with code,
so I'm not sure what piece is missing. Any ideas from
anyone? Also since Douglas mentioned that this is a
violation of relational database theory, is there maybe a
way to get each of the selected items to store in
different fields instead? For example, If the user
chooses Animals, Insects, and Nature, could Animals be
saved in Topic1, Insects in Topic2, and Nature in
Topic3? I really want to only use one list box if at all
possible.

Thanks, Tara
 
oops, I forgot to set the ctl object variable. Just
before the 'For Each...' line insert;

Set ctl = Me!MyListBoxControlName
 
I got the same message. I'm not very familiar with code,
so I'm not sure what piece is missing. Any ideas from
anyone? Also since Douglas mentioned that this is a
violation of relational database theory, is there maybe a
way to get each of the selected items to store in
different fields instead? For example, If the user
chooses Animals, Insects, and Nature, could Animals be
saved in Topic1, Insects in Topic2, and Nature in
Topic3? I really want to only use one list box if at all
possible.
Click to expand...


AACK! That would be an even worse design.

What you have when you want to put multiple entries in a field is a
one-to-many relationship between those entries and the rest of the record.
Proper design therefore dictates a new TABLE be created where each of these
entries now has its own ROW.

Then you would use a subform on your existing form and be able to make as
many (or as few) of these entries "related" to the main record as you need.
 
I followed all of the code to the best of my
understanding, but still dont seem to have it pointing to
the correct field some how as it is not populating the
new 'txtTopic' field with the selections from the
original list box.

any thoughts aobut what i might have confused?

it is my understanding that the original listbox should
have the 'control property' set to the new
field 'txtTopic'

The new field 'txtTopic' should have the control property
set to the field on the table... in this case 'topic'.

is this correct? And if so, what would cause it not to
populate the txtTopic field that was created?
 
Back
Top