Microsoft Access INDIRECT and Data Validation Commands

Question

I’d like to use a command similar to the INDIRECT and Data Validation commands in Excel, where the field values to choose from in a table change based on the value of another field in the same table. 

Simple example below – if in the country field USA is selected, then the city field would show only those cities in the US.

Country

USA

Canada

USA Cities

Chicago

Detroit

Canada Cities

Toronto

Windsor

Answer 1

I’d like to use a command similar to the INDIRECT and Data Validation commands in Excel, where the field values to choose from in a table change based on the value of another field in the same table. 

 

Simple example below – if in the country field USA is selected, then the city field would show only those cities in the US.

 

Country

USA

Canada

 

USA Cities

Chicago

Detroit

 

Canada Cities

Toronto

Windsor

You can do this on a form by using what are often called "cascading combo (or list) boxes.  There's a discussion of two ways to do that on The Access Web here:

    http://access.mvps.org/access/forms/frm0028.htm

    Forms: Limit content of combo/list boxes