Can options in one listbox depend on what is selected in another?

[Names]

Hello.
I am quite new to Access and in designing a database using Access 2000 I
have met this problem:
When entering data about certain products within a form about "Order
details", there is one step where the user selects the category of a product
from a list box (called "Category"). There are separate tables and forms
specific to each category and all of them use the "Product ID" field as the
primary key (I have one table with details specific to category1 and a
separate table for details about the products classified under category2
etc). Below the Category listbox is a box where I would like all the Product
IDs of the products in the category chosen to appear once the user selects
the category.
So it's: Choose category -> IDs of all the products in that category appear
in the listbox below -> Choose ID of product
1) I'm not sure how to do this.

I would like the Product ID selected (in the last step mentioned above) to
be stored in the "Order details" table. 2) How might this be done?

3) Supposing the user finds that the product being entered is new (i.e. is
not on the list of products already added to the category table, does not
have an ID yet). I have tried getting a macro to open the relevant form
(according to the selected category). After the product details are keyed
into that form and the Product ID is assigned, will this new product show up
in Product ID field on the "Order details" form so that the user can select
it?

Thanks in advance!

 

[KARL DEWEY]

Search on 'Cascading Combos'.

 

[Tom Wickerath]

Implementing a cascading listbox is analogous to cascading combo boxes, as
long as the Multiselect property of the listbox is set to None. This is
fairly easy with a proper database design.

There are separate tables and forms specific to each category...

You should not have separate tables specific to each category. Your data is
not properly normalized, and it will be A LOT more difficult to implement the
idea of cascading combo or list boxes with this design. I suggest that you
read some of the database design papers available at this link, especially
the first two papers written by database design expert Michael Hernandez:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Names]

Wow. Thanks. I'll read up about that.

Sorry I don't really understand the information about normalization. I'm not
sure how I can arrange the data other than to have separate tables specific
to each category. Different information is required for each of the different
categories, for example, for a product under Cateogry1, its mass and ink
color are relevant, while for a product under Category2, its age and material
are relevant. I created separate tables for such category-specific details.
I'd really appreciate help on an alternative way to do this.

More details on how I'm hoping it'll work:
When a user is entering data about orders into the "Order details" form, he
will enter the products ordered by first selecting the category to which it
belongs (chosen option saved in Order details table), then he should choose
the Product ID from the list that appears as described (still trying the
cascading list/combo box), and the Product ID chosen should be stored in the
Order details table. And then there is the issue on what should happen if the
Product ID is not on the list, as you might have seen in the original post..

Thanks!

 

[Tom Wickerath]

I would add a CategoryType field to this table, so that you could record
which type of category each record belongs in. You may even want to have a
lookup table of CategoryTypes, with a CategoryType foreign key in your
Products table.

As for the different entities that you need to track, the simplist solution
is to simply add these fields to the same table: Mass, InkColor, MfgDate (in
order to calculate age) and MaterialType. As you add records to this table
with different category types, you will have "holes" in the table. I call
this the swiss cheese effect. That's not particularly a problem. You will
likely have different forms, each based on a query that includes the
appropriate criteria to filter for the correct CategoryType, and only
displays the applicable fields.

The other option, which is quite a bit more work, is to establish child
tables related one-to-one (1:1). So, you would have a related child table
that includes a primary key or unique index that matches the primary key in
your Products table and includes the Mass and InkColor fields. You'd have
another table that includes a primary key or unique index that matches the
primary key of the Products table and includes the MfgDate and MaterialType,
etc. Breaking all of these fields into separate tables is going to be a lot
more work, with probably no real benefit, but I mention it as a design
alternative.

Work on getting a good solid database design before worrying about the
functionality of your forms. The design is analogous to the foundation for a
building--it must be solid to keep everything else that you build from
collapsing under its own weight.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Names]

Brilliant! Many thanks. I shall try.

 

[Names]

Hi all,

Here is a useful website for information on cascading combo boxes:
http://office.microsoft.com/en-us/access/HA011730581033.aspx

Thanks Karl

: