Adding a series to a Chart Object in Access MS Chart

Hi Experts,

I have a chart problem. It must be easy to do, but I cannot seem to figure out how to do it.

I have a chart control in an Access 2007 VBA application. At runtime, the chart is created, and data is added.

The user is able to select whether they want 1, 2, or 3 lines on the chart. It works fine if they only want 1 or 2 lines, but at runtime, when II attempt to add another series, I cannot.

The code looks like this:
'declarations
    Dim ch As Chart
    Dim cs As SeriesCollection
    Dim ser1 As Series
    Dim ser2 As Series
    Dim ser3 As Series

'I get the chart series from the Chart object, and set it to my series
    Set ch = Me.chart1.Object.Application.Chart
    Set cs = ch.SeriesCollection
        Set ser1 = cs.Item(1)
        Set ser2 = cs.Item(2)
'fail on this next line
        Set ser3 = cs.Item(3)

I guess my question is: How do I make sure there are 3 items in the SeriesCollection? Or, How can I add a 3rd Item to the SeriesCollection if count < 3 ? This is in Access 2007, not Excel.

Thanks for your help.
WestCoastHipAsked:
Who is Participating?
 
jefftwilleyCommented:
Its been my experience that you must add the Max number of series that you anticipate having on the chart in the data grid in design view.  Then you manipulate the ones that will have data.

Let's say you add 12 columns in your series. If your recordset returns 3 columns, then the remaining 9 columns will simply be null, or not exist. The control handles the display, the data drives the series in use.

Make sense?

J
0
 
Jeffrey CoachmanMIS LiasonCommented:
Can you take a step back and explain a bit more about this chart?

Why does this need to be done at run time?
How many series' are in the raw data?

This might be clearer if you posted a sample:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.


JeffCoachman
0
 
WestCoastHipAuthor Commented:
jeffT,

You are correct. The issue was that I was formatting the chart object BEFORE I set the rowsource (series).

It works when I set the rowsource, and then format the series. I found absolutely no way to manually set the series before the rowsource. Even drinking a few beers did not help! Can you imagine?

Thanks again for your advice
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
WestCoastHipAuthor Commented:
Thank you boaq and jefft. I appreciate you advice on this chart question. I learned that the main thing with charts is that you must set the rowsource, BEFORE formatting the chart series (I am now setting the rowsource before doing anything with the chart).
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, great, ...but I am still  bit curious as to why this needs to be done at runtime...?

JeffCoachman
0
 
WestCoastHipAuthor Commented:
boaq,

There are many reasons that things must be done at runtime.

For example, if the user would like to format the series according to his own preferences, that must be done at runtime. If the user wishes to change the algorithm for the series calculation, that must be done at runtime.

Cheers



0
 
Jeffrey CoachmanMIS LiasonCommented:
That can all be done from user selections before hand, but that is the subjetc for another question...

;-)

Jeff
0
 
WestCoastHipAuthor Commented:
boaq,

If the user decides to have a factor of 12.8723578623 as opposed to 14.78978927344 applied to the data, how would I determine that?

Cheers

0
 
Jeffrey CoachmanMIS LiasonCommented:
I don't know what a "Factor" is in your example...

But if your situations warrants making changes in runtime, then roll with it...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.