Cascading fields in a form

[peekaboo_01]

From previous visits, I know this is a common thread, but I'm too dense to get it, I guess.

My form contains several fields the need to have input, some are text boxes and some are combo boxes with drop down menus.

When field "UNIT" is filled with DTX (from a drop down menu), I want the table DTX CTY to be the drop down choices for "SUB-UNIT" field. When "UNIT" is filled with MTX, I want table MTX CTY to be the choice for "SUB-UNIT".

So they cascade...pick DTX = DTX CTY, MTX = MTX Cty, etc. Any suggestions?

 

[dkinley]

Here is the thread you are looking for ...

http://www.access-programmers.co.uk/forums/showthread.php?t=154680


It has a sample db attached.

-dK

 

[dmonney]

Put this in your On Update event for the field UNIT

If(Unit.Value = "MTX") then
Subunit.Rowsource = "SELECT [MTX Cty].[YOUR FIELD NAME] FROM [] ORDER BY [Your Field Name in table];"
Else 
Subunit.Rowsource = "SELECT [DTX CTY].YOUR FIELD NAME FROM [DTX CTY] ORDER BY [Your Field Name in table];"
end if
subunit.requery

 

[peekaboo_01]

THANKS GUYS!!!

I found the coding exceptionally helpful...I actually understood it!!!
I did, however, have to add some additional brackets to make it work:

SELECT DISTINCT [tblUnit_IDs].[UNIT], [tblUnit_IDs].[DEPT] FROM [tblUnit_IDs];

I had the SELECT DISTINCT code from another source (the link) and when I added that it all worked!!!

Thanks a MILLION!!!