I have a crosstab based on a query. Depending on the selection criteria used in the base query the crosstab will generate different column headings, predictable in advance. For example if the user chooses 'Fruit' the crosstab column headings will be 'orange, apple, pear' and if he chooses 'Vegetable' they will be 'onion, potato, carrot'. Can I set these column headings dynamically with VBA so that I can use just one crosstab for all user selections? Creating a different crosstab for each possible user selection and hard-coding the column headings in the crosstab properties isn't a good idea as the fruits might not always be 'orange, apple, pear'.
I'm using Access 2007.