I'm working on a database of cars. I have one database called CARS. It contains the fields CAR_ID, YEAR, MAKE and MODEL. I have another database called ENGINES. It contains the fields ENGINE_ID and ENGINE SIZE. And my final database is CARS_ENGINES. It has the fields CAR_ID and ENGINE_ID. I have a join from CAR_ID to CARS_ENGINES_ID and one from ENGINES.ENGINE_ID to CARS_ENGINES.ENGINE_ID. My database CARS_ENGINES has all the combinations of the models and engines. The problem I'm having is how do I set up my main database to do the following:

I want to be able to have the fields YEAR, MAKE, MODEL and ENGINE. When I click on YEAR a list of all Years in the database are displayed. When I click on MAKE I want a drop down list that shows all the Makes for only the year selected. Then When I click on MODEL I want a drop down list that displays only the Models for that Make of car for thay year. And subsequently, the ENGINE field would display the engines that were available for that year and model car. I have spent countles hours trying to achieve this without success. Any help would be most appreciated.

Do a search for +conditional +value +list and you will find lots of posts on this.

Also, John Mark Osborne has a good example of exactly what you want. Click HERE and type in 'Multi-Level Conditional Lists' into the search box.

OK I downloaded the file and set my database up accordingly with the value lists. But my problem still remains. I can get it to display the makes for the year indicated. But when I go to Models it still lists all the models for that make, not just the ones made in that year. It seems like I need to be able to filter by make and year. How is this accomplished?