SubForm using Crosstab Query (Dynamic Column Problem)

[KINGOFCHAOS17]

Hello all. I'm trying to create a subreport linked by field [CatalogID] from a crosstab query. The columns of the query are dynamic and I'm struggling to get the subreport to function correctly when the data changes in field [Year]. I've attached screenshots of the crosstab query and of the data it shows. Is anyone able to help me make the columns dynamic so they're not fixed to the initial years when I create the subreport? Thanks in advance.

 

[arnelgp]

the report is Dynamic so you Set each Report Label and textbox controlsource dynamically through vba code.

 

[arnelgp]

see the Open event of the report on this demo.
see also the report in design view.

 

[KINGOFCHAOS17]

see the Open event of the report on this demo.
see also the report in design view.

Thank you, I've tried applying this to a Form, however having problems with the data fields showing an error. What am I doing wrong?

 

[arnelgp]

I thought you want it in a Report/Subreport (post#1)?

 

[KINGOFCHAOS17]

I thought you want it in a Report/Subreport (post#1)?

Oh wow, that’s totally my mistake, sorry. I’d been working on reports all week and had them on the mind. I’m this scenario/requirement it’s a subform.

 

[arnelgp]

you can use the Subform's Source Object property to set it to your Query.
then it will take whatever column you have on that query.
see Form1.
view Child0 subform in design view.

 

[KINGOFCHAOS17]

Thank you. I can't see any design details for the Child0 subform, I was hoping to replicate this process with other similar data.

I imported the subform Child0 into my form and tried linking on ID and CatalogID however I received the attached error. I can't see / don't understand what I'm doing wrong.