Multiple Lookup

[Guest]

Hi, Database interface question here. What I have is a table with fields
System and Subsystem. These are both integers which are Ids which reference
another table called System_Subsystem_Lookup which contains System-subsystem
pairs like the following:

systemId |system |subsystemId |subsystem
-----------------------------------------------
1 SystemA 1 SubsystemA1
2 SystemA 2 SubsystemA2
3 SystemB 3 SubsystemB1
4 SystemB 4 SubsystemB2

Where in the Main table systemId and subsystemId are stored.
What I want is when the Main table is viewed, either directly or by query, I
want comboboxes for both systemId and subsystemId. For systemId they will
display all the possible systems with the text from the System field (not the
id #). I can do this part.

What I cant do is have subsystemId display a combo box with ONLY the
subsystems from the system selected in that specific row.

I've tried to do this with a user form and using VBA to catch the events but
it has been very messy.

Any Ideas????

 

[MacDermott]

You're on the right track to use a form, rather than a table, for this task.

You might look here for ideas:
http://www.mvps.org/access/forms/frm0028.htm