I'm back. Sill having trouble getting my drop downs to populate correctly. Cascading Combo boxes?

I'm using the Test Form for Disciplinary Tracking Table. Need the user to select a Location which will provide the list of departments for that location. And then the selected department would provide the list of sub departments. I've tried it a couple of different ways -everything in one table-everything in smaller tables. I'm close-have the list of all the departments showing but can't get it to show only the ones for that location. Thought it might be something I need to add in the SQL query?? What am I missing??

I'm a newbie at this (and blonde) so I need very simple directions.

I'm attaching the DB.

Any help would be greatly appreciated. Thanks All!

I would first of al sit down and make a few changes. First I would remove all of the spaces from all of the names, like HR Mgr change that to HRMgr. Next reove all of the MS special characters from names, like Location ID# change to LocationIDNo and change the - to _. I have got it to manually load location for the departments, but this needs to be automatically selected. Have not figured it out yet. I have to leave shortly, maybe someone else here can get the link corrected. I did make a few minor changes, but I left most untouched. hth.

Thanks Quest! That's a good start. I'll see what I can do from there. At least I'm seeing the correct list and not all the duplicates. Appreciate your help! Have a good one!

You have this setup as a many to many relation but I don't see that has existing. What it looks like is that department IDs for Location 1 start with 1, Location 2 with 2, etc. For the SubDepartments, the integer value represent the Department and the decimal value the subdepartment. These are One to many relations, not many to many. Therefore, the tbl-LocDept and tblDept-sub tables are unecessary.

What I would do is something like this. The Location combo is no problem. I would set the RowSource of the Dept combo to:
SELECT [Tbl Dept].[Dept ID], [Tbl Dept].[Dept Name] FROM [Tbl Dept] WHERE (((Int([Dept ID]/10))=[Forms]![Test]![Location]));

I would do something similar for Sub dept:
SELECT [Tbl Sub Dept].[Sub-Dept ID], [Tbl Sub Dept].[Sub-Dept Name] FROM [Tbl Sub Dept] WHERE (((Int([Sub-Dept ID]))=Forms!Test!Department));

Finally, I would add a Requery to the After Update event of each previous combo to requery the next one.

That should work for you. It works in my tests.

Good one, Scott. I just couldn't get that part work when I ran out of time. The whole thing didn't work right because I forgot the Int. Just shoot me.

{S bang (IMG:http://pages.prodigy.net/indianahawkeye/newpage06/9.gif)
Actually its a very nice structure because it doesn't require specific foreign keys (I've made note of it for future reference). By incorporating the higher level in the ID of the lower level, you can use an expression to create your filters.



Edited by: ScottGem on Thu Nov 18 11:35:39 EST 2004.

Interesting thanks for the tip.