Solved

Adding a series to a Chart Object in Access MS Chart

Posted on 2011-03-20
9
965 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

737 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