Combo Boxes with dependent queries

[K Roger]

I have an Access form which has 3 combo boxes where the results of the 1st one determines the contents of the 2nd drop down box and the 3rd box choices are determined by the results of the 1st and 2nd queries.

Specifically, the first box is a list of states, the 2nd a list of counties and the 3rd a list of townships or other MCD's.

There are 50 states, 3150 county names, and 35,700+ names's involved.
The 1st box contains the states. Then a "SELECT DISTINCTROW" narrows the counties down to a number found in the particular state chosen, The 3rd box then has a "SELECT DISTINCT ROW" that further narrows the choices of MCD's dependent upon the results of the 1st two combo box choices.

Currently the queried file is flat containing all three desired fields.

Specifically:

The 1st box is a straight forward combo box picking 1 of 50 states

The 2nd box Row/Source code used is: SELECT distinctrow [qryCo].[countyname] FROM qryCo where [qryco].[state] = [forms]![frmLiatrisAllCondensed]![statex] order by [qryCo].[countyname];

The 3rd box Row/Source code used is: SELECT distinctrow [qryTwp].[MCD],[qryTwp].[countyname],[qrytwp].[state] FROM qrytwp where [qrytwp].[state] = [forms]![frmLiatrisAllCondensed]![statex] AND [forms]![frmLiatrisAllCondensed]![county1] = [qrytwp].[countyname] order by [qrytwp].[countyname], [qrytwp].[mcd];

The problem is:
The code works for the first time a choice is made.



However thereafter, the 2nd and 3rd boxes do not change and remain the values for the first state picked during the session.
The only workaround with the current code I have found is to exit the form and reenter then it - a very impractical situation.

What am I not doing code-wise or not doing at all.

Any help would be appreciated. I am familiar with using ACCESS but only moderately familiar writing my own VB code

BTW - I would eventually like to normalize the file (break it into 3 files) because of other data needed that is unique to one of the three files.

Thank you K Roger

[orange]

BTW - I would eventually like to normalize the file (break it into 3 files) because of other data needed that is unique to one of the three files.

It is important to normalize your database now... not later.
Getting the structures correct will simplify many tasks.

See the first few topics at this site for normal forms, normalization and Entity relationship diagramming
http://www.rogersaccesslibrary.com/forum/topic238.html

As for cascading combos, watch this free video tutorial.
http://www.datapigtechnologies.com/f...combobox2.html

[alansidman]

In addition to the links provided by Orange, here is one more that is helpful to understand cascading combo boxes.

http://www.fontstuff.com/access/acctut10.htm

I agree with Orange about Normalization. Do it first and it will avoid issues later.

Alan

[K Roger]

Thanks to the 2 respondents that assisted me with the problem.

The requeries on updates helped the situation but did not totally resolve the issue. I added a requery to the combo boxes within a "got focus" event and that seemed to resolve the non-changing values completely. Is there some reason that a "got focus" event should not be used as a requery action other than the possible redundancy and overhead involved with this action?