I am using VBA, not VB to generate dynamic combobox list. The three combobox list come from the three columns of a Access table by using SQL select DISTINCT....

Intension: After all the list are populated, user is able to select from one of the combobox. Once the user select the item, the list in the other two are changed according to the relationship in the Access table. Otherwise, the user could make the combination which does not exist in the database. Do you have any idea how to do this? 1) I was thinking of using first combo_change to control the list of other two, but it mess up the combobox list when I do combo.clear.

2) how to control the list of other two? Shall I still open the database to run SQL again?

Any thoughts are grateful. Thank you very much.

The SQL in Access is pretty well integrated into the sytem. You can put many things into your SQL that would not fly outside Access.

One thing you can do is put calls into the SQL of functions existing in modules in the DB: The DB system is optimized so that if the function receives no arguments, or receives a constant, the function is run only once, and the answer is stored, being inserted into each row of the output.
But if the function receives a variable argument (such as the contents of a field), the function will be run once per row.
If you wrote a function that read the value of combo 1, you could then call that function from the WHERE clause of the query that is the rowsource for combo 2. (You could build in a default value of, say -1 for cases where combo 1 is empty.)
Then the Change event of combo one could run cboTwo.Requery.
This way, combo two tends to its own business (with a little help from a User-Defined function).

Thank you very much, BrianWren, haven't got chance to try your method as I finally come up the solution of using code to generate Pivot table, and use the output to call other functions. Thanks again.