Columncount on Chart

[Guest]

Hi All,

I have a question relating to a chart on a form. This question came up in a
prior post discussion I had with Rob Parker but I have decided to post as a
new question.
Basically I need to change the number of columns of data the chart is
showing, for example I have a Bar chart which shows sales by month & I need
to display count of sales by month as well. When I am in design mode on the
form & click on the chart I can manually change the Colomncount value. At run
time I get the following errors;

Me.OLEObjectName.Object.ColumnCount = 3 Give Error: 438 "Object doesn't
support this property or method

Me.OLEObjectName.ColumnCount = 3 Gives Error: 2448 "You can't assign a value
to this object"

* When I refer to the charts visibility, rowsource etc I use the
following(no errors);
Me.OLEObjectName.RowSource =""

* When I refer to the charts type, title etc I use the follwoing(no errors);
Public objMainChart As Graph.Chart
objMainChart..HasTitle = True

* I have a reference to "Microsoft Graph 11.0 Ojbect Library".

All the documentation says that the ColumnCount is not readonly & can be
changed. If someone has a work around or another method I would be forever in
your debt.


Thanks in advance.

Cheers,
Adam.

 

[Rob Parker]

Hi Adam,

Me again ;-)

Sorry about taking so long to get back to you, and I'm afraid I haven't got
good news. I've just found enough time to spend a couple of hours playing
with charts in both forms and reports (using Access 2002), with rather
interesting results.

First, and directly related to your question regarding ColumnCount, I can't
find a way of accessing this property via VBA (even though the Help files,
and information on the MSDN site strongly implies otherwise). Other
properties of the chart object can be accessed, either directly or via the
..Properties syntax. Here's some examples, cut/pasted directly from my VB
Editor

Me.TestChart.Properties("BackColor") = 8454143 'this works - when
TestChart is Enabled
Me.TestChart.BackColor = 8454143 'this also works
'but following commented lines don't work.
' Me.TestChart.ColumnCount = Me.tboxColCount 'RTE 2448 can't assign a
value to this object
' Me.TestChart.Properties("ColumnCount") = Me.tboxColCount 'RTE 2448
can't assign a value to this object
'Errors on preceding two lines are not the same as when identical code
(except referring to Forms("frmName").tboxColCount) is run in Report!!!
' Me.TestChart.Object.Properties("ColumnCount") =
Forms("frmChartTest").tboxColCount 'RTE 2771 - the object you tried to
edit doesn't contain an OLE object
Me.TestChart.Properties("RowSource") = "tblChartTest2" 'this line
works in Form, but not in Report - gives RTE 2455 - invalid reference to the
property RowSource
Me.TestChart.RowSource = "tblChartTest" 'this line works in Form, but
not in Report - gives RTE 2455

The Form and Report were not bound to any recordsource; the Chart objects in
both were bound to a table as their row source - this could be changed by
VBA code for the form, but not for the report (IIRC, Duane Hookum mentioned
this problem in the previous thread). Make of this what you will.

Finally, and perhaps of use to you, is the following example of changing the
rowsource for a chart in a report via VBA, by a couple of steps. This code
was run from a button on my chart tes form, but you could put it elsewhere.

Private Sub btnReport_Click()
'Last edited: 3 May 2005, Rob Parker

DoCmd.OpenReport "rptChartTest", acViewDesign, , , acHidden
Reports("rptChartTest").TestChart.RowSource = "tblChartTest2"
DoCmd.Close acReport, "rptChartTest", acSaveYes
DoCmd.OpenReport "rptChartTest", acViewPreview
End Sub

HTH,

Rob

 

[Guest]

Thanks for replying Rob, I really appreciate all your help. It is quite
interesting & frustrating that Charts are treated differently on reports
compared to forms.
The information I found on MSDN & with the internal help also implied you
could change the columncount, we will have to leave it as a great mystery.

To overcome changing the column count issue today I worked out to directly
hide & show comlumns of data on the datasheet of the chart. This seems to be
a fair work around.

Thanks again Rob,
Adam.

 

[Rob Parker]

You're welcome. I'm sorry we couldn't find a solution, rather than open
another can of worms. But even that's adding to my, and your, knowledge
base.

It would be nice if an MVP could post a definitive answer, but given that
your post of this question (with a specific Subject line) was 3 days ago, I
suspect it's now unlikely.

Rob