Synchronized Multi Value Lookup Boxes

[Diz]

Total newbie to Access here, but I have done considerable work in Excel, VBA,
etc.

Anyway, I'm trying to make what should be a very straightforward database.
Basically, each entry will contain an attachment (PDF, word, ppt, etc.),
filename, file description, and 3 layers of categorization for each
attachment.

The categorization process will look something like this:

Layer 1 (category): need ability to choose multiple categories

Layer 2 (sub-category): each category in layer 1 has x number of
sub-categories. I would like to populate the list of options for layer 2
based on the categories selected in layer 1. I also need the ability to
choose multiple categories in layer 2.

Layer 3 (sub-sub-category): each category in layer 2 has x number of
sub-categories. I would like to populate the list of options for layer 3
based on the categories selected in layer 2. I also need the ability to
choose multiple categories in layer 3.

Given that multiple cateogries can be chosen in each layer, I would like to
use multi value lookup boxes for each category column. Ideally, I would
create a master list of all possible category names for layers 2 and 3, and
then limit the list based on the categories marked in the layer preceding it.

I can't figure out how to do this though!

Hopefully this makes sense. Help would be very much appreciated!!!!

 

[XPS350]

Total newbie to Access here, but I have done considerable work in Excel, VBA,
etc.  

Anyway, I'm trying to make what should be a very straightforward database..  
Basically, each entry will contain an attachment (PDF, word, ppt, etc.),
filename, file description, and 3 layers of categorization for each
attachment.

The categorization process will look something like this:

Layer 1 (category): need ability to choose multiple categories

Layer 2 (sub-category): each category in layer 1 has x number of
sub-categories.  I would like to populate the list of options for layer2
based on the categories selected in layer 1.  I also need the ability to
choose multiple categories in layer 2.

Layer 3 (sub-sub-category): each category in layer 2 has x number of
sub-categories.  I would like to populate the list of options for layer3
based on the categories selected in layer 2.  I also need the ability to
choose multiple categories in layer 3.

Given that multiple cateogries can be chosen in each layer, I would like to
use multi value lookup boxes for each category column.  Ideally, I would
create a master list of all possible category names for layers 2 and 3, and
then limit the list based on the categories marked in the layer precedingit.

I can't figure out how to do this though!

Hopefully this makes sense.  Help would be very much appreciated!!!!

The first thing to do is te design a proper database structure (tables
and relations) for this situation.

You need tables to store the possible category names and tables to
store the entries and related categories.

I think you will need for the first part:
tblCategoryLevel1
tblCategoryLevel2
tblCategoryLevel3
with 1-to-many relations between the levels.

The second part requires:
tblEntry
tblCatLevel1ForEntry
tblCatLevel2ForEntry
tblCatLevel3ForEntry
The last three tables wil have a relation both with entry and one
category tables.

I do not think it is possible to create "multi value lookup boxes".
For each category (level1) you create a new record in
blCatLevel1ForEntry. You can use a subform to do this. It is possible
te create a subform on the subform to enter level2 to categories
connected to the selected level1 category. Its also possible to limit
the number of level2 categories to categories that belong to the
choosen level1 category.

Main thing for now is the right structure.

Groeten,

Peter
http://access.xps350.com