?
Solved

Adding a series to a Chart Object in Access MS Chart

Posted on 2011-03-20
9
Medium Priority
?
970 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 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 1600 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

764 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