Change chart in subform (1 Viewer)

[AndyMD]

Hello,

I have a form AdmMenu with a combobox Period and 4 options "By day", "By month", "By quarter" and "By year" and a subform SubMenuAdm.
When I chose a value in the combobox, I want the subform to populate with a chart BChart depending on the choice, I have tried to change the Tranformed Row Source on the chart BadgeChart from the form BChart but I get the error I can't changed the property on a read-only form.

I could create a form/report for every case but I rather want to find a solution with less forms/report.

Private Sub Form_Open(Cancel As Integer)
Select Case TempVars!Answer
Case 1
Me.BadgeChart.TransformedRowSource = "SELECT Format([StartTime], 'DDDDD'), Sum([Brep]) AS [SumOfBrep] FROM [BListing] GROUP BY Format([StartTime], 'DDDDD'), Year([StartTime])*12 + Month([StartTime])-1 ORDER BY Year([StartTime])*12 + Month([StartTime])-1"
Case 2
Me.BadgeChart.TransformedRowSource = "SELECT Format([StartTime], 'mmm 'yy'), Sum([Brep]) AS [SumOfBrep] FROM [BListing] GROUP BY Format([StartTime], 'mmm 'yy'), Year([StartTime])*12 + Month([StartTime])-1 ORDER BY Year([StartTime])*12 + Month([StartTime])-1"
Case 3
Me.BadgeChart.TransformedRowSource = "SELECT Format([StartTime], '\Qq 'yy'), Sum([Brep]) AS [SumOfBrep] FROM [BListing] GROUP BY Format([StartTime], '\ Qq 'yy'), Year([StartTime])*4 + DatePart('q', [StartTime])-1 ORDER BY Year([StartTime])*4 + DatePart('q', [StartTime])-1"
Case 4
Me.BadgeChart.TransformedRowSource = "SELECT Format([StartTime], '\Qq 'yy'), Sum([Brep]) AS [SumOfBrep] FROM [BListing] GROUP BY Format([StartTime], '\Qq 'yy'), Year([StartTime])*4 + DatePart('q', [StartTime])-1 ORDER BY Year([StartTime])*4 + DatePart('q', [StartTime])-1"
End Select
End Sub

Do not be aware about the accolade " or ' they are maybe wrong or missing but this is not the issue yet.
The TempVars!Answer is to use the chosen option in case this one need to be used inside the subform rather than the form with the Combo.

 

[CJ_London]

what do you mean by a 'read only' form?

And please use code tags (highlight the code and click the </> button) to preserve indentation

Use debug.print to see what the value of your tempvars!Answer actually is and the the sql you are creating actually looks like

1. I suspect you need to alias your select fields e.g.

Format([StartTime], 'DDDDD') As Period
Year([StartTime])*12 + Month([StartTime])-1 AS Start

2. and this

Format([StartTime], 'mmm 'yy')

is an illegal string, try replacing the the middle ' with two '

Format([StartTime], 'mmm ''yy')

3. And try using the form load event rather than the open event

 

[AndyMD]

what do you mean by a 'read only' form?

And please use code tags (highlight the code and click the </> button) to preserve indentation

Use debug.print to see what the value of your tempvars!Answer actually is and the the sql you are creating actually looks like

1. I suspect you need to alias your select fields e.g.

Format([StartTime], 'DDDDD') As Period
Year([StartTime])*12 + Month([StartTime])-1 AS Start

2. and this

Format([StartTime], 'mmm 'yy')

is an illegal string, try replacing the the middle ' with two '

Format([StartTime], 'mmm ''yy')

3. And try using the form load event rather than the open event

Hi thanks for the reply, there is no possibility to change the TranformedRowSource on a bounded object in a subform, so I figured a solution.
I have made copies of the chart in my BChart form and run a code in On Load who checked TempVars!Answer and unhide the hiden charts.