Solved

Adding a series to a Chart Object in Access MS Chart

Posted on 2011-03-20
9
953 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:WestCoastHip
  • 4
  • 4
9 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 35179241
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 400 total points
ID: 35180408
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
 

Author Comment

by:WestCoastHip
ID: 35183328
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
 

Author Closing Comment

by:WestCoastHip
ID: 35183351
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35183885
OK, great, ...but I am still  bit curious as to why this needs to be done at runtime...?

JeffCoachman
0
 

Author Comment

by:WestCoastHip
ID: 35185606
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35185687
That can all be done from user selections before hand, but that is the subjetc for another question...

;-)

Jeff
0
 

Author Comment

by:WestCoastHip
ID: 35186102
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35186650
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now