Trouble getting appropriate data

[thebiggermac]

I'm having trouble with two of my tables relating to each other. I have one
table named Systems and a second table named Sub-Systems.

The Systems table provides a general listing of topics such as Hydraulic
System, Electrical System, Water System, Fuel System and so forth. My sub-
system table breaks down the primary systems with additional data. Examples:

Fuel System:
Fuel Tanks
Fuel Gauges
Fuel Transfer Unit
and so forth.

Electrical System:
AC System
DC System
Circuit Breakers
Batteries
Low Voltage System
and so forth.

What I want to do is select the primary system and then list ONLY those items
that is appropriate to that system. Such as Fuel System:Fuel Transfer Unit.

Currently I can display all the sub-systems but cannot break it down or link
the appropriate items of the specific primary system.

I have set up autonumbers in both tables and tried to join the tables as to
read only those items that are equal to the primary systems. Didn't work.
Basically I have tried all types of joins to solve my problem only to
conclude that approach is not going to work. Also I tried to set up
subdatasheets in the primary systems table and while that works to some
degree (only in the table) it does not allow me to select the sub-system I
need on the form. Hence I am at a loss for solutions. I know there is a
simple way of making it work but I can't see it.

Any assistance would be most appreciated. Thanks.

 

[Jeff Boyce]

You said the magic word ... "form".

You can use what is called "Cascading Comboboxes" (do a search on this
term).

The general idea is that you create two comboboxes (your situation). The
first combobox lets you pick a "system". In the AfterUpdate event of that
combobox, you requery the second combobox.

The second combobox used a query that refers to the first combobox for a
selection criterion for the "System". Because this second combobox is being
requeried after a system is selected, the second combobox only lists
sub-systems for the selected system.

Regards

Jeff Boyce
Microsoft Office/Access MVP