Linking Tables to a Field Dynamically based on the value of another field

[apchampa]

Here is my issue...



I have a field, let's call it "Item Type", and it is linked to a table called "List-Item Type" which contains the values that you can select for this field.

Now, depending on what item you select I want another field, let's call it "Item Sub Type", to link to specific tables associated with each Item Type.

For Example........ If an individual selects "Computer" from the Item Type list then I want the Sub Type field to link to the "List-Computer Types" table which then will contain something like Laptop, Desktop, Server, etc.

But, if an individual selects "Mobile Phone" from the Item Type list then I want the Sub Type field to link to a completely different table called "List-Phone Types" which will have values like Smartphone, etc.

I hope I'm making sense. I'm assuming the only way to do this is some sort of script........ but it would be super cool if Access had a built-in way to quickly do it.

Thanks for your time.

[June7]

Requires VBA to change the combobox RowSource SQL statement.

Or use a UNION query that merges info from all of the 'Type' tables as one dataset to serve as source for combobox RowSource.

Why are the types in different tables? Are the fields really so different?

[ItsMe]

What you are describing sounds like a composite key. When you have one record and it takes two fields combined to create a unique key, that is a composite key. I use code to manage referential integrity and constraints for tables with composite keys.

[apchampa]

I wanted to keep things in different tables so that the user only sees specific choices and they can't accidentally choose something I don't want them to...... I could use one big list but then they would have to hunt & peck to find what they want, maybe not find the best match and select something different, etc. Easier to only put in front of the user a limited set of choices.

[orange]

apchampa,

Do yourself a favor and work through this tutorial on database and table design. You will learn how to design your tables and relationships based on your business requirements. Tables don't get selected/created arbitrarily or by magic, they are based on your business needs. Spend 45-60 minutes on the tutorial --the best use of your time at this point.

For a broader overview see this link

More info on Normalization

[June7]

Conventional approach would be 1 table then manage what users are presented with by filtering. Review http://datapigtechnologies.com/flash...combobox2.html