select multiple items from dropdown

[vc-programmer]

hello everyone,

i have a table which is populated using a form. one of the fields is
person's name, the dropdown for which picks up data from a second table
(which has an index and person's name).

what i want to do is modify the form so that a user can select multiple
items from the dropdown, but i havent been able fo find a way to do so.

i am using access 2003. i know that access 2007 lets you do that, but i dont
have an option of upgrading right now.

i would appreciate your suggestions

thanks,
vc-programmer

 

[Allen Browne]

Create a related table, where you can store many options for one record in
your existing table (i.e. a one-to-many relation.)

This would be the best approach, even if you were using Access 2007.

If that's a new concept, here's a couple of introductory examples:
http://allenbrowne.com/casu-23.html
http://allenbrowne.com/casu-06.html
a free tutorial:
http://allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf
and some other links:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[George Nicholson]

A combo box control (aka "drop down") only allows the selection of a single
item. A list box allows multiple selections.

A combo box is designed to combine the type-a-single-value textbox with some
of the preexisting list features of a listbox (hence the name Combo). With
the right settings, a combo allows the addition of a new value to the list
(similar to a text box) but a list box provides the user with no built in
method of adding to the provided list.

Afaik, none of this has changed in Access 2007, nor would any change be
desirable(IMO).

Afaik, if you want/need to allow multiple selections, you have to use a
listbox.

 

[vc-programmer]

allen: thanks for responding to my query. i am going through your suggestion
to make it work, i will send a reply soon. the tutorials are well written, my
compliments.

george : thanks for suggesting another way of doing it. i seem to be doing
something wrong to make it work, i have provided details of what i was
trying.
- a main table is populated with data using a form. one of the fields to be
populated is a person's name, which is pulled from another table by a look-up
which has names and serial numbers auto-numbered.
- i earlier had a listbox in the form, this allowed me to enter one name.
- i now created a combo-box in the form using the wizard. what happens is
that i select a name from the drop-down, it gets populated in the combobox.
when i select another name, the previous name in the combo-box gets
over-written.

what am i doing in-correctly here? i want to be able to enter multiple names
into the field. i guess i am missing something here...

thanks for your input.

 

[vc-programmer]

george, i feel like an idiot for not reading your post carefully.

i have been using list-boxes, but i have not been able to select multiple
items in a form to populate a filed using that. i tried using combo-boxes,
but that dosent work either ( like you mentioned in your post )

for access 2003, is there a way to select multiple items in a list box?

 

[George Nicholson]

- i earlier had a listbox in the form, this allowed me to enter one name.
Listboxes have a MultiSelect property. If set to Simple or Extended multiple
selections are allowed, but the default is None.

when i select another name, the previous name in the combo-box gets
over-written.

And this is bad because??

The only time I can imagine you would NOT want a combobox value to NOT
overwrite existing data is if you were trying to use one combobox for both
data entry and record navigation (which is a lousy idea: use 2), but you
haven't even hinted that's what you are trying to do, so I'm confused.
Sorry if I'm being dense. If you really do want to make multiple
selections, you need a listbox.

 

[vc-programmer]

And this is bad because??
what i want to do it use a dropdown list of names to select multiple names,
and then populate this to a field in a database.

Listboxes have a MultiSelect property. If set to Simple or Extended multiple
selections are allowed, but the default is None.

i set the multiselect properties to extended. when i select multiple items,
and hit enter - the field i linked it to does not get populated . do i need
to set something else too?

 

[Rick Brandt]

what i want to do it use a dropdown list of names to select multiple
names, and then populate this to a field in a database.

i set the multiselect properties to extended. when i select multiple
items, and hit enter - the field i linked it to does not get
populated . do i need to set something else too?

Access does not make what you want to do simple because it is a bad idea to
do in the first place. A field should hold ONE piece of data. For multiple
pieces of similar data you create a new table with a relationship to the
first (one to many).

If you insist on ignoring this basic premise of database design then you
will have to do some work to make it happen. That means using code to grab
the values from your multi-select ListBox and doing something with them to
get them inserted into your field. Then you have even more difficult work
(with more code) to make such a field editable with your form.

 

[vc-programmer]

everyone on this thread : thank you for responding and providing guidance to
proceed in the correct direction. and not only that, it was amazing to see
super-fast responses as well.

i need to start with the basics of relational db, and thats where i will go .

thanks,
vc-programmer