How to create a multiple selection list box in Access

[Melody]

Hi,
Using Access 2000
Skill level - new

I'm trying to create a simple database to catalog DVDs. I have most
everything set up the way that I want. However, I would like to make a
category selection drop down list where more than one value can be selected.
Once selected, I would like those to show up in the form field so as I am
scrolling thru the database I can see what categories a particular movie is
in. For example, say I go to record 13 in the database, looking at the
categories field, I can see this particular movie is in two categories,
Biography and Drama.

Ultimately, I want to be able to run three reports from this database that I
can print out. A list of movies by category, by star or by number (the DVD's
will be labeled and stored in numerical order on the shelf)

Can anyone help me to set up the category field to accomplish the above?
I'm hoping it's not too complicated as my skill level is newby. Hope this
makes sense

Thanks.

 

[Albert D. Kallal]

When you place a list box on a form, you "bind" it to one field.
Unfortunately this means them that you can not make the list box
multi-select.

The instant you turn the list box into a multi select list box, then you'll
be able to select multiple values, but MS access cannot automatically put
that into a single field for you (you not be able to bind the field to your
table. (by the way, access 2007 can in fact do this for you without having
to write any code at all).

However, you have access 2000.

Because you are new to MS access, I really don't want a post a solution with
a whole bunch complicated code, and force you to start firing up the code
editor just put a few categories into your database.

Hence, we have to compromise our design a bit, and we're not going to able
able to use a list box.

what we'll have to do here is use what is called a sub form.

Can I assume that your already have a table of categories now that you want
to use? let's assume that you are to have this table and it set up like the
following

id: (autonumber id)
Catagory: (standard text field).

Hence, build the above table. Then, open up that table and enter in all the
categories such as drama, science fiction, romance etc you plan to use tinto
the catagory field.

Now for each DVD in your collection, we're going to need a place were we can
"store" the selections (catagores) for each DVD.

Thus we will simply build a table, we can call it

tblDvdCatagores

id: (autonumber - not needed, but lets still have this field)
catagory_ID (this will be set to the id of the catalogry
dvd_id (this is what dvd this reocrd belongs to).

this table called DVD categories will thus be related to your table of dvds.

We will place this new table as a sub form in our DVD form. For the
category_ID, we build a combo box based on our table category.

Thus for each additional category you enter, you'll have to go to a new line
in the sub form, and simply select a value or category from a combo box.

The above sequence and construction is not too hard, but will be a bit of a
challenge for new user to access. One advantage of the above approach is
that you'll not have to write any code. If you really really wan to use a
list box and allow the uses to click several ones at the same time, then
you'll have to write some visual basic code to do this (or make the jump to
access 2007 where the ability to have multiple selections stored in the
field is automatic).

 

[Melody]

Thanks for your reply. I think I understand how to accomplish what you have
written below. I'm not quite sure I understand selecting the categories once
it's all set up. How do you set the subform up to go to a new line to select
another category?

 

[Albert D. Kallal]

Thanks for your reply. I think I understand how to accomplish what you
have
written below. I'm not quite sure I understand selecting the categories
once
it's all set up. How do you set the subform up to go to a new line to
select
another category?

Actually, what will happen is you "always" will see the extra line in the
sub-form, and you should be able to simply select a category from the combo
box you made for that sub-form.

You have:

Main form: DVD (regular non continues form)

Move Name : __________ Director __________
Year _____________ ...and so on...

subform: (continues form)

combo box:Romance
combo box:Science Fiction
...... (you can enter as "many" categories in the sub-form as you need/want
*

In the continuous sub form we build,, you can see how the combo box repeats
over and over..and on the "last line", you see the "*" (asterisk) and the
combo box that not yet set with a value. This user interface is not as nice
as a list box, but you can still click on the combo box (the last one on the
new record line,, and then select a category. You can do this click on
combo box over and over for as many new categories you need. It still quite
fast, and it still click select...click select...click select. It certainly
quite easy to do.

So, build a continues sub-form based on our tblDvdCatagores, and in that
continues form build a combo box for the category based on your table of
categories.