Automating graphs in a report (.maximumscale)

[davejam]

Hi all,

I am really struggling trying to auto format charts!!!

originally my problem was when charts had to many categories of data, so i came up with a way of splitting the data into seperate pages and linking the charts parent / child fields so i would get a different report per page.

ok, my problem is setting the maximum scale of the chart, as each chart will have a different value range then the auto maximum will keep changing across pages so am trying to get vba to do it.

i've got as far as this

Me.Graph10.Object.Application.Chart.Axes(2).MaximumScale = CDbl(17000)

which although looks right, and with everything i've found on the web should work... although theres also been mention of 97 a lot....

I'm using access 2000, does this, or should this still work??

The error i get is:
run-time error '2771'
The bound or unbound object frame you tried to edit doesn't contain an ole object

Any help or pointers would be greatly appreciated, think i've spent the last few hours trawling the net and trial and error just to do something that seems like it should be easy!!

Cheers

daveJam

even my shrink says its all your f#@/ing fault

 

[dhookom]

I would try to get rid of some of your expression. For instance, I have code on a form to change the chart type:

    Me.grpGraph.Type = Me.cboGraphType

Additional code hides/displays the legend

    Me!grpGraph.HasLegend = Not Me!grpGraph.HasLegend
    If Me.grpGraph.HasLegend Then
        Me.grpGraph.Legend.Position = -4160
        Me.grpGraph.Legend.Shadow = True
    End If

and code to change the title

    Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value  '"""" & Me.txtTitle & """"

Duane
Hook'D on Access
MS Access MVP

 

[davejam]

right, am i missing something very basic here

when i try simplifying i get an error no 438
"Object does not support this property or method"
Me.Graph10.axes(2).maximumscale = CDbl(17000)
Me.Graph10.maximumscale = CDbl(17000)

Even when i try
Me.Graph10.charttitle.Text = "tester"

i get the same error...

i have tried it both with and without Microsoft Graph 9 referenced...

Is there another way to achieve what i'm doing.

I have a chart which is required to show data for many open ended date ranges bringing back potentially hundreds of results (for the columns)

After about 20 the graph really starts to struggle and then not all columns are shown, so my idea was to split it down using a paging column in the table and coding it specifically page 15 rows for each page number. (so for 40 different types there would be 3 pages defined in the recordsource)

Then i have a recordset on my report that selects distinct values from the table (so returns 1, 2 and 3)

Then i create a chart but link it parent / child using the pageno field so for each page i get a bar chart with 15 columns in (the final with 10)

but unfortunately the results can obviously vary between the charts so having auto for maximum causes the charts to be of differing scales (which does not read well)

so my plan was to get a figure for the greatest result and round this off the the nearest whole 100 / 1000 / 10000 and use this to set the max for the chart.

I need to do it on the fly they are not one off reports!!

Any ideas on maybe a different way, or suggestions on my errors...

cheers

Davejam

 

[davejam]

right, it was something very simple....

ended up putting the full text for it as was just trying to get something working, and one thing i hadn't mentioned i don't think, i was trying to do this on report open.

well just thought, for a laugh cos i found a bit of code hiding on the web and that seemed to be in report_activate.

Hey presto, it works

i'm gutted i wasted so much time on it but truly happy its over!!!

daveJam

even my shrink says its all your f#@/ing fault