In Parameters in External Data Queries, Nick Hodge shows how to change a query table based on input from a cell. In this example, one parameterized query will be based on another parameterized query. That is, when you change one, they both change.
Start with two query tables on the same worksheet. These two tables are the Categories and Products tables from Northwind.mdb. The Categories query table will have a prompt-type parameter in which a user can enter the desired category name to show the description for that category. The Products query table will have a cell-based parameter that shows all the products for the selected category.
The CommandText properties for these query tables look like
FROM ‘C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind’.Categories Categories
FROM ‘C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind’.Products Products
The first query table’s parameter was changed to show a meaningful prompt. The second table’s parameter was changed to point the Category ID cell in the first table.
When the first table is refreshed, the user is prompted for a category name. The second table will be refreshed to show all the products for that category automatically.