Populate subform dynamically

[a_ud]

I've got a subform that needs to be populated dynamically.

Pls have a look at the attached DB. It has:

• 2 main tables, S1 and S2 (3 fields each, TraitA to C for S1, and TraitD to F for S2).
• A form with a combo where you select one of the fields (say, Trait_A).
• A subform where I want to show all the current values of the chosen field (Trait_A) and the # of occurrences for each value of the field (an example of the needed query -just for TraitA- is included too). That is, a simple Group by query.

Question is: how do you modify dynamically the SQL query that populates the subform? It's easy to define a fixed query that populates the subform, but: How do you modify the data source of the subform so it does use the query for the chosen field on the main form? Through VBA?

Ideally the solution would do 2 things:

1) Define a query for the subform (I guess, through VBA and setting up a Recordsource). This SQL would be similar to this:

SELECT Trait_A, Count(Trait_A) FROM table GROUP BY Trait_A
but I don't know how....

2) Link (or create) the 2 fields in the subform to the said query (one showing the variable, the other showing the # of occurrences).

Any ideas?

Thanks in advance, a.


PS: ideally, the solution needs to 'build' (not call) a query for the subform (the reason being that the real problem doesn't have 6 fields in total, but hundreds...).