How to limit the value list of one field based on the value of a previous field
I have been working on a database to store my bird watching records. I have been using Access to do so up until now. I have three tables that I am using - a table of locations, a table of a list of all the birds that are found in the US, and the main table with all my trip records.
The Location table consists of LocationID, Location, State/Province, County, and Country
The Bird List table consists of BirdID, Bird Name, BirdType, BirdCode, and Region
The Trip Records table consists of the RecordID, Date, Location, BirdName, Status, Season, and Comments and three unbound fields - Country, State, and
The main layout is for the TripRecords table. On this layout I want to add two fields - Country and state - to use to limit the locations field to only those of a specific country and state. For example, If I visited a location in Colorado, I would want to select the US as the country which would limit the state field to only those states in the US that I have visited. I would select Colorado from the list which would then limit the Location field to only those sites in Colorado that I have visited.
Also, on this main layout I want to be able to limit the the Bird Type field according the region that the location is in. From the Bird List table I would limit based on the region field and the word Both. For example, in Colorado, the bird type list would consist of all birds found in the west region and of birds found in both the east (rarities and vagrants) and the west. I would use this list of bird types to limit the bird name field to only the selected bird type in the bird type field. For example - I could select Colorado from the state list. This would set the region field to west. This value of west and the word both are used to limit the bird type to types that are found in the west. I could select the bird type = eagle from the list and this would limit the bird name field to only Bald Eagle and Golden Eagle.
In Access I did this through using queries to populate the drop down lists for the Country, State/Province, BirdType, and BirdNname fields. Since FMP 10 Advanced does not use queries, I am having a hard time figuring out how to do it in FMP 10 Advanced. Can Someone help me?