Crosstab query, I want to select column using list

[survivo01]

I have a crosstab query called Enrolled crosstab. ABE, GED, Conflict represent a column in the crosstab query. I want to be able to select the column with a drop down list
Enrolled crosstab
ABE
GED
Conflict

Field	ID	ABE
Table	Enrolled crosstab
Sort
Show
Criteria		is null

What I can't figure out how to do is change the red field to ABE, GED, Conflict

Here is the Crosstab query
Enrolled
ID
Mod
Enroll type
TRANSFORM First(enrolled.[Enroll Type]) AS [FirstOfEnroll Type]
SELECT enrolled.ID
FROM enrolled
GROUP BY enrolled.ID
PIVOT enrolled.[Mod];

FIELD	ID	MOD	ENROLL TYPE
TABLE	ENROLLED	ENROLLED	ENROLLED
TOTAL	GROUP BY	GROUP BY	FIRST
CROSSTAB	ROW HEADING	COLUMN HEADING	VALUE

The result is

[Dal Jeanis]

Post the full SQL for the crosstab query.

[survivo01]

Here is the Crosstab query
Enrolled
ID
Mod
Enroll type
TRANSFORM First(enrolled.[Enroll Type]) AS [FirstOfEnroll Type]
SELECT enrolled.ID
FROM enrolled
GROUP BY enrolled.ID
PIVOT enrolled.[Mod];

FIELD	ID	MOD	ENROLL TYPE
TABLE	ENROLLED	ENROLLED	ENROLLED
TOTAL	GROUP BY	GROUP BY	FIRST
CROSSTAB	ROW HEADING	COLUMN HEADING	VALUE

[Dal Jeanis]

Okay, so there are three methods and any method is about the same amount of work.
1) The first is the simplest, conceptually. It's not elegant, but it has the advantages of simplicity and certainty.

You can code four different crosstabs and have the dropdown cause the source of the subform display to be replaced with the selected query.

2) The second requires a little bit more comfort with code, but, again, it can be accomplished with certainty.

You can set up VBA to take the SQL for one crosstab, and have the spot in the SQL now held by Mod instead be provided by a string variable that contains the name of the desired field.

3) The third would require some testing, but would be the most elegant of the three methods, if the syntax of a crosstab query would allow it. I can't guarantee that it will, because crosstabs are finnicky.

You can have the dropdown set a temp variable, and have an IIF set the value of your Pivot field based on the value of the temp variable.

Something like this...
IIF([TempVars]![CrossChoice]="Mod",enrolled.[Mod],[TempVars]![CrossChoice]="ABE",enrolled.[ABE],...)