Check box to hide chart series

[Joe Small]

Hello everybody,

I am fairly new to access and need some help getting this done.
I have created a graph in a report that shows 2 lines(one pink, other black), each line representing a field of a query table. I want to create a check box that will hide one line(say pink) when unchecked and display the line when checked. I plan on adding more lines to the graph so I need to know how to use check boxes to add/remove line to the graph as the user wants.

 

[boblarson]

Hello everybody,

I am fairly new to access and need some help getting this done.
I have created a graph in a report that shows 2 lines(one pink, other black), each line representing a field of a query table. I want to create a check box that will hide one line(say pink) when unchecked and display the line when checked. I plan on adding more lines to the graph so I need to know how to use check boxes to add/remove line to the graph as the user wants.

It isn't real simple but I've attached a sample I created for you to hopefully help you get it. I'm using the orders table from the Northwind sample database for this example. See if you can look through at the various parts including the VBA code behind the scenes and make sense of it.

 

[Joe Small]

Thanks Bob,

I see that in the sample you gave me, there is a field[ShipCountry] in the table where the various countries are contained and you are using chkboxes to select them through the "Where In" clause. The query is a crosstab query.

My graph is based off a query, not crosstab but the fields look like this;
Date| Avg1| Avg2| Avg3| Avg4|

My graph has x-axis time and all 4 averages are plotted against time with legend that shows different colors for each Avg.
In the query if the "Show" box is unchecked for say Avg1, Avg3, those graphs don't show on the graph output.

Can I write an EventProc or Macro or Expression through a checkbox placed on the report to kind of check them in the query to be included in the graph and uncheck to be excluded from the graph?
I am trying to figure out a way to create it as you've done though.

 

[boblarson]

Post a copy of your database (with bogus data of course) and I'll try to do it in there. I probably won't get to it until Monday at the earliest.

 

[Joe Small]

Bob,

I have attached a sample.zip file.

 

[boblarson]

1. You can't do the selections from the report - you'll need a form to do it because the report needs to close, open in design view, change the SQL, and then reopen and you'll lose the values in the checkboxes when that happens and those are necessary.

2. So, I gave you a form which should be open with the report like in my sample.

 

[boblarson]

Oh, and I changed it from REMOVE to INCLUDE and set the defaults so that they would be what you originally had. It makes coding simpler that way.

 

[Joe Small]

Bob,

Thanks a lot. I think this should work. I should be able to incorparate it into my project.

 

[boblarson]

Best wishes for the rest of your project.

 

[Joe Small]

Bob,

Thanks very much. I finally have my project done,Tested and working great. I had to take time to understand the code you provided, just my style, and work with it.

I am happy and looking forward to work on more Access/VBA stuff.