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.
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.