?
Solved

Adding a series to a Chart Object in Access MS Chart

Posted on 2011-03-20
9
Medium Priority
?
981 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…
Suggested Courses

862 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