?
Solved

General MS Graph problems in Access 2007

Posted on 2009-12-28
24
Medium Priority
?
1,231 Views
Last Modified: 2013-11-27
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
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_25001927.html?cid=1575#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.  
0
Comment
Question by:wsturdev
  • 15
  • 9
24 Comments
 
LVL 1

Author Comment

by:wsturdev
ID: 26133590
P.S. Have to go into meetings for several hours but will check back as quickly as possible.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26134569
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26135442
<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?
0
Industry Leaders: 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!

 
LVL 58

Expert Comment

by:harfang
ID: 26135534
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26137986
<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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26138963
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26140859
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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26141820
That is worrisome. Sorry, I don't know where to look; tell me how it goes.
(°v°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26143110
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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26143244
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26145253
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?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26145588
References to .PlotBy cause errors.  I have a reference to Microsoft Graph 12.0 Object Library.  Should I have another reference as well?
0
 
LVL 58

Expert Comment

by:harfang
ID: 26150939
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

0
 
LVL 1

Author Comment

by:wsturdev
ID: 26161632
Well, I have most of this working now:
  1. Plot all entries as filtered
  2. Clear chart
  3. When number of points is > 12, plot all in one color (Offer option to vary by color)
  4. When number of points is 12 or less, vary by color (Offer option to plot all in one color)
  5. Switch between XY Scatter and Bubble
I have a few final things to work on, and have a few questions as a result:
  1. 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?
  2. 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?
  3. 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?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26161785
<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!
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26162214
<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
 
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26162783
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
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26162795
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.
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 26164247
Well, it looks as though you don't need me any more! Good job!

Changing a property of a collection is often implemented internally as a loop. It should be slightly faster than changing the same property element by element, because refresh issues should be optimised, but that isn't always the case.

However, for Bubble charts, there is global size option affecting all bubbles in all series.

(°v°)
    cht.ChartGroups(1).BubbleScale = 150

Open in new window

0
 
LVL 1

Author Comment

by:wsturdev
ID: 26165773
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?
0
 
LVL 58

Expert Comment

by:harfang
ID: 26167758
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°)
0
 
LVL 1

Author Closing Comment

by:wsturdev
ID: 31670532
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.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26181256
FYI, I see by the Help data that modifying anything more than the marker and the font of the legend entries is not possible.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26181397
Thanks and success with your charts! -- (°v°)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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