Start Free Trial

asked on

General MS Graph problems in Access 2007

I have an app built in Access 2007. At one point, I have an MS Graph object on a sub form. I set the row source type as "Table/Query" and the row source as a SQL query against a local table. The make-up of the query depends on the objective to be shown in the graph.

Depending upon my needs, I change the graph type from XY Scatter to Bubble Chart as needed and coordinate the query to support the type.

Frequently, but for no reason I have been able to determine, even though I have proper data in the Datasheet as a result of the query, the graph seems to be "disconnected" from the datasheet.

I have been having a long discussion in
https://www.experts-exchange.com/questions/25001927/Crosstab-Query-with-Decimal-column-Headers-for-MS-Graph-Datasheet.html?cid=1575&anchorAnswerId=26128011#a26128011
but now must close that out. Based on help I got there, I know I have the correct organization of data in my datasheet for an XY Scatter (X,Y,S). Here is a sample:
Business_Score      Technical_Score      Application_Name
2.724206      2.748611      AAAAA
3.425397      3.417583      BBBBB
3.32619      3.274214      CCCCC
3.452381      3.372123      DDDDD
3.04881      3.363636      EEEEE
3.225397      3.363636      FFFFF
3.68718      3.542456      GGGGG
3.971825      2.971705      HHHHH
4.360714      3.276933      IIIII
There are actually 86 rows in the datasheet. Every cell that should be filled is.

But, when I look at the graph, there are only 5 points plotted, none of which relate to any of the values in the datasheet.

At other times, when I set the row source query to produce data for a Bubble Chart, and then try to change the chart type to Bubble Chart, I either get a message saying I must have 2 or more points to plot before I can change it to Bubble Chart (there are 86 in the datasheet) or I get an object error. Then, still other times, it works as expected.

I have compacted and repaired the accdb numerous times, and have built a new accdb and imported all components. This problems comes and goes.

How does this "disconnect" happen and more importatnly what steps can I take to prevent it? I must be able to control this so my users do not run into it.

ASKER

P.S. Have to go into meetings for several hours but will check back as quickly as possible.

Hi again,

In previous versions, a Graph object would save the result of the last query in its data-sheet at every run. This was catastrophic: the form was silently saved each time it was opened, causing massive bloat (you needed to compact a run-time database at the very least every day).

Now the "design-time" data-sheet is disconnected from the row source. You design the chart with whatever data is available, and it gets replaced with the query data only at run-time. In other words, in order to design a chart, you need to provide plausible data "manually". I usually do this by opening the query, copying the entire data-sheet (or a reasonable portion of it), and pasting it into the Chart's.

This makes it difficult to use the same chart object for different sorts of results. I would suggest you use one for XY data, and another for Bubbles. Either on two different form, or by making one of them invisible (and without row source).

The perceived "disconnection" at run-time can be simply the symptom of an error: if you try to feed only two columns to a bubble chart, for example, this will generate an error. Perhaps the default data is used instead when that happens? If no data is returned, the chart should be entirely blank.

(°v°)

ASKER

<I would suggest you use one for XY data, and another for Bubbles.> I like this idea, and had been kind of leaning that way, but just had not implemented it.
<I usually do this by opening the query, copying the entire data-sheet (or a reasonable portion of it), and pasting it into the Chart's.> I suspect one of the things that is happening to me is, while I start with a "clean" chart/datasheet, during my develoopment and testing, the chart/datasheet gets modified, and potentially saved in whatever state it is in at the point I force the end to my code, for example. Then, when I test the next time, the chart/datasheet is not what I expect it to be. I wonder if I can avoid that by programmatically resetting the chart/datasheet to a known condition upon oprning the form? What are your thoughts?
<The perceived "disconnection"> In the one situation I was talking about, I let the chart build and processing complete, then double clicked on the chart. The datasheet showed 86 perfectly formatted rows representing 86 points, all within the range of the scales of the axes, but the chart only plotted 5 points. It was like the chart was plotting points from a datasheet different than the one I could see. Any further thoughts on that?

Normally, the Graph object doesn't save the "run-time" data at all. So you shouldn't have to do anything to "reset the datasheet", ever. Unless you are using versions 97 or earlier, that is.

About the chart not displaying some values, I really don't have any good ideas. You can disable rows and columns (grey them out), but you should get a visual clue about that condition. Some chart types require a precise number of columns, this could be another problem. They also need to be numbers (not numbers as text)... And the orientation (data in rows or in columns) must match the data.

I never had this problem. Does it occur only at design-time?

(°v°)

ASKER

<Does it occur only at design-time?>
I will compile the accdb into an accde and check it out, but will not be able to do that until about 6 hours from now.

I think I misunderstood what you were saying. Do you double-click the chart with the form in design view, or in form view? It's just that I never do that, so I don't know much about this mode (my charts are usually disabled in the interface -- only for viewing).

(°v°)

ASKER

I was saying I double click them in form view and see the datasheet with good data but the chart shows nothing. But, once I have finished develoopment, I will disable it before creating the accde.

That is worrisome. Sorry, I don't know where to look; tell me how it goes.
(°v°)

ASKER

Still working on this... How do I indicate programmatically that the data is in rows or columns?
In Design view, I set the chart to Data in Columns and put in some sample data. Then in form view, the points plot but do not appear correct. I double click on the chart, then click Data in Rows, then immediately click Data in Columns, then the chart shows up correctly.
I am hoping I can specify the orientation of the data programmatically.

It should be something like this. If your char object is acxChart:

With acxChart.Object
.PlotBy = xlRows
.PlotBy = xlColumns
End With

Add a reference to the MS-Graph Library, else the constants are 1 and 2 respectively.

(°v°)

ASKER

I have a reference to the MS Graph Library, and read the entire MSDN MS Graph VB Reference and on this page <http://msdn.microsoft.com/en-us/library/aa208303(office.11).aspx> I see the reference to PlotBy, but it is not selectable and there is no example.
Even with the reference to MS Graph Library, I cannot seem to see any Properties or Methods when I am coding. Is there something more I should have on my computer that might help me?

ASKER

References to .PlotBy cause errors. I have a reference to Microsoft Graph 12.0 Object Library. Should I have another reference as well?

The Object property of the ActiveX container renders the object anonymous. It is not known at design time what object (if any) will be returned.

To get "intellisense" -- technically to use early binding -- you need to assign the object to a typed Graph.Chart object (see below). I added a chart called Graph0 and a button called Command1, and indeed didn't find the PlotBy property.

It turns out it's a property of the application object... one of the small differences between the Excel Chart and Graph Chart object models.

(°v°)

Private Sub Command1_Click()
    Dim cht As Graph.Chart
    Set cht = Graph0.Object
    cht.Application.PlotBy = xlRows
    cht.Application.PlotBy = xlColumns
End Sub

Open in new window

ASKER

Well, I have most of this working now:

Plot all entries as filtered
Clear chart
When number of points is > 12, plot all in one color (Offer option to vary by color)
When number of points is 12 or less, vary by color (Offer option to plot all in one color)
Switch between XY Scatter and Bubble

I have a few final things to work on, and have a few questions as a result:

Retrofit my routines to allow selecting or deselecting one entry at a time for plotting. I will have to maintain a sense of the sort order so that bubbles stay in order with the largest at the back. I think I know what needs to be done here, but do you have any thoughts/guidance?
On the XY Scatter, the user wants to be able to change the size of the markers. How do I do that? Can it be done with a blanket statement, or must it be done one marker at a time?
In my other post, you made a comment about a good use of color in a bubble chart <with dozens or hundreds of applications would be to show one additional information, e.g. a category. You could also "tag" certain applications based on a criteria: blue bubbles background, red bubbles corresponding to the user's current criteria or selection...>.

I spoke to one of the major users and he thinks it would be great if I colored the bubbles based on relative size. For example, assume I have 3 relative sizes for bubbles. I would like to color the largest ones in blue, the next smaller in Red and the smallest in Green.

How can I do that? Can it be done with a blanket statement?

As an extension to that, he would also like to colorize based on other criteria. For example, the bubbles could be colorized based on relative size, but then the user could specify that certain bubbles be colorized in other colors to draw attention to them.

How can I colorize selected bubbles individually?

ASKER

<Retrofit my routines to allow selecting or deselecting...> This was easier than I thought. Since the rowsource for the chart is being used, all I have to do is a Save after checking or unchecking the "Include in Chart" checkbox!

ASKER

<change the size of the markers>
Figured out how to do that with either a blanket statement: GraphObj.SeriesCollection(1).MarkerSize = Me.Marker_Size
Or point by point:
For I = 1 To GraphObj.SeriesCollection.Count
GraphObj.SeriesCollection(1).Points(I).MarkerSize = Me.Marker_Size
Next

ASKER

There is a problem with what I laid out in my most recent post...
My solution for changing the marker size with a blanket statement works if there is only one series, such as when I do a simple query that produces X,Y,S.
But, in order to get XY points plotted in different colors, I have to use the Pivot type query, which produces as many series as there points to be plotted. Then, to change the marker size, I have to loop through the points, as above in "point by point", and it must be modified as follows:
For I = 1 To GraphObj.SeriesCollection.Count
GraphObj.SeriesCollection(I).MarkerSize = Me.Marker_Size
Next
It would be nicer if this were more "instantaneous". Is there a way to change all points of multiple series in one command, something like:
GraphObj.SeriesCollection(all).MarkerSize = Me.Marker_Size

ASKER

This works as a single command:
GraphObj.SeriesCollection.MarkerSize = Me.Marker_Size
and this works as a single command:
GraphObj.MarkerSize = Me.Marker_Size
but they are both about the same speed as looping and doing each one separately.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Well, I must admit I am more in control now than I used to be, but I still have at least one additional question...
I know how to control the positioning and width and height of the Legend. But I need some guidance in controlling the contents of the legend. Sometimes, the entries in the legend are single spaced vertically. But sometimes, they are double or triple spaced vertically. How can I control that?

Try the following: create a similar chart to what you like in Excel, and then record a macro while you modify the chart. The object model is (almost) identical to the one used in MS-Graph, which explains why constants start with 'xl', so you might be able to find a solution by examining the recorded macro.

(°v°)

ASKER

I think I've got it! Your guidance has been most helpful and I think I have a pretty good handle on things for now

Anyone looking at this solution should look through the entire thread, not just this "accepted solution", where much helpful information is available.

ASKER

FYI, I see by the Help data that modifying anything more than the marker and the font of the legend entries is not possible.

Thanks and success with your charts! -- (°v°)