Tables with Lookup queries

[Guest]

I have a library table. When entering the data for the County, I want it to
lookup from the County table and have a list to choose from. That works just
fine.

The next field is Municipality. I have a Municipality table for lookup, but
I only want to show the Municipalities that come from the County just chosen.

This is what I have, but it doesn't work.

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities RIGHT JOIN Counties ON Municipalities.CTYCode =
Counties.CTYCode
WHERE (((Municipalities.CTYCode)=[Counties]![CTYCode]));

Any help would be greatly appreciated.

Thanks

 

[Ron2006]

I believe you can simplify it significantly. Try this:

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities
WHERE Municipalities.CTYCode = forms![thenameoftheformyouare on]![thenameofyourcitycodefield];

There is really no need for the join to the city table for the
condition is simply that the municipalities are part of the city
chosen in your prior combo.

Ron

 

[Ron2006]

I believe you can simplify it significantly. Try this:

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities
WHERE Municipalities.CTYCode = forms![thenameoftheformyouare on]![thenameofyourcitycodefield];

There is really no need for the join to the city table for the
condition is simply that the municipalities are part of the city
chosen in your prior combo.

Ron

looking at it again, I am not sure I got the names correct on the
WHERE clause.

Basicaly, you want to limit the query of municipalities to only those
that have a county that matches the combo box selection. No other
tables/queries need be involved.

Ron

Ron

 

[Guest]

Thank you Ron....that worked perfectly!

I believe you can simplify it significantly. Try this:

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities
WHERE Municipalities.CTYCode = forms![thenameoftheformyouare on]![thenameofyourcitycodefield];

There is really no need for the join to the city table for the
condition is simply that the municipalities are part of the city
chosen in your prior combo.

Ron