Checkbox to hide / show series in embedded chart

[Walshie]

Hi All,

I've spent the last two days trying to figure this out - searching all over the web!

I'm fairly comfortable with vba / access but this one has got me stumped!

I have chart (grpMaster) that is embedded in a form (frmMain), the chart is a line graph based on a query (qryDataFilter) with 4 data series.

What I want to do is have various checkboxes (chk1 to chk4) that if selected show the series and if no don't - It seems straight forward!

Could someone point me in the right direction?

I've tried SeriesCollection() but this comes up with errors. (Object doesn't support this property or Method).

The easiest way i can think of is just to hide / show the series line on the chart, but can't figure this out?

I'm not expecting someone to write the code for me just provide an overview and then I will look further into it.

Any info would be apprectiated!

Cheers
Chris

 

[LeonDavies]

Hello,

Just tried searching for an answer to this and came across the following:

Do a search on this site for "Series Collection Chart Problem" it was a similar (if not the same) query as yours posted by Scott Oh.

Hopefully it helps or at least points you in the right direction

 

[Walshie]

Thanks Leon,

I actually found a solution, instead of trying to edit the chart I actually changed the SQL source code depending on what checkbox was checked, this now works perfect.

When I get to work tomorrow i'll add my code to this thread as a possible solution for others

Cheers
Chris

 

[LeonDavies]

Ooo if you could that would be a big help.

I'm currently working On a project that requires interactive charts but am a week or so off developing that element so would be good to see how you achieved it

Many thanks

 

[Walshie]

Hi Leon,

Apologies for the delay, bit of a busy start to the week!

Here's the code:

SQL = "SELECT ""Wk"" & (Format([WeekCommencing],""ww yy"")) AS WeekNo"

If Forms!frmMain.chk4wk = True Then
SQL4Wk = ", Sum(qryDataFilter.[4wkAverage]) AS 4wkAverage"
End If

If Forms!frmMain.chk13Wk = True Then
SQL13Wk = ", Sum(qryDataFilter.[13wkAverage]) AS 13wkAverage"
End If

If Forms!frmMain.chkDemand = True Then
SQLDemand = ", Sum(qryDataFilter.[Sales]) AS Demand"
End If

If Forms!frmMain.chkPhysical = True Then
SQLPhysical = ", Sum(qryDataFilter.PhysicalStock) AS PhysicalStock"
End If

SQLEnd  = " FROM qryDataFilter GROUP BY ""Wk"" &  (Format([WeekCommencing],""ww yy"")),  (Year([WeekCommencing])*12+Month([WeekCommencing])-1);"

SQL = SQL & SQL4Wk & SQL13Wk & SQLPhysical & SQLDemand & SQLEnd

Forms!frmMain.grpMaster.RowSource = SQL

Basically when I toggle each check box on or off, the graph rowsource is updated. I have the above stored as a function in a module and for each of my checkboxes I have "Call UpdateGraph" in the onclick event.

If your unsure with the SQL, set the graph up with all of the lines plotted, then view the rowsource and you can copy and paste the snippets of code.

Hope this helps and if you need more info please let me know

Cheers
Chris