Desperate for MS Graph alternative

[Oldsoftboss]

Rachael, see you are from Victoria, from Melbourne or the country ?

Dave

 

[Rachael]

Hi Dave,

I'm in the bush between Wangaratta and Mansfield.

It seems that in this game sometimes you just have to resign yourself to some things like Microsoft stuff ups because trying to get anything done about it is impossible. So, with the chart resizing issue I have changed my database to 1024x768 resolution, which allows the charts to sit fully on the page and not chop things off. I have advised all my clients to use this resolution but have included scroll bars for those who still want to use 800x600. What do you do!!!!!!!!

I have found the charts in Access to be extremely fickle, my main use is comparing data from different tables by date on the same chart ie overlaying the same data from irrigation applied, rainfall, soil moisture etc to get comparisons. This entails controlling the x axis via code. I have an unbound field for start and finish date to set the charts date x axis (five charts with transparent backgrounds overlaying each other) then to select the appropriate records in the query.

Private Sub Report_Activate()

Dim objChart1 As Graph.chart
Dim objAxis1 As Object
Set objChart1 = Me.YourChartName.Object
Set objAxis1 = objChart1.axes(xlCategory)


If objAxis1.Type = 1 Then
objAxis1.minimumscale = (Forms!MoistIrrigChart![Date1])
objAxis1.maximumscale = (Forms!MoistIrrigChart![Date2])
End If

End Sub

I make lots of backups and often have to revert to them as I find for no known reason that I can see they will just stop working or start giving errors. I wish someone would come up with a better option for charting, there are a few third party charting tools but cost $$ and I already have over 200 clients so trying to get ocx or dll's or whatever onto their machines (they are all mostly farmers aswell) would be a nightmare.

With OLE messages - this might be a throw away comment - I repair my Access installation and this usually fixes some things - sometimes I completely remove it and reinstall!!!

Anyway, sounds like you have a monumental task, a couple of charting tools I looked at were flipper graph tool and Tee-chart, they looked OK but again my dilemma of so many clients already and my lack of understanding of the nuts and bolts of computers has restricted me to shitty ol' MSGraph.

Cheers, Rachael

 

[Oldsoftboss]

Beautiful spot, the high country, I have a sister in Albury and a neice in Yackandanda, but dont get up there nearly enough.

Back to our frustration...

I see you use:

Private Sub Report_Activate()

Dim objChart1 As Graph.chart
Dim objAxis1 As Object
Set objChart1 = Me.YourChartName.Object
Set objAxis1 = objChart1.axes(xlCategory)

If objAxis1.Type = 1 Then
objAxis1.minimumscale = (Forms!MoistIrrigChart![Date1])
objAxis1.maximumscale = (Forms!MoistIrrigChart![Date2])
End If

End Sub

I presume this is to limit the date range on the graph. My question is: If I use

Set objAxis1 = objChart1.axes(xlCategory)

Do I have to declare (xlCategory) anywhere, or are you using xlCharts?

If I can adapt this, my work will be cut by 3/4

Dave

 

[Oldsoftboss]

After a bit of fiddling, I set Microsoft Graph in the references and now get an error on the 3rd line

Dim objChart1 As Graph.chart
Dim objAxis1 As Object
Set objChart1 = Me.YourChartName.Object
Set objAxis1 = objChart1.axes(xlCategory)

Set objChart1 = Me.YourChartName.Object

The bound or unbound object frame you tried to edit doesn't contain an OLE object !!

I just about give up

Dave

 

[Rachael]

Hi Dave,

Try not putting the code in the detail section of the report but in the header section, I seem to remeber a similar thing when I was trying to get all this to work. You also need to reference Graph 9 Object library

Cheers, Rachael

 

[Oldsoftboss]

Not sure what you mean by not putting the code in the detail section as the code is in the Report_Activate.

Or are you refering to the graph itself.

Dave

 

[Rachael]

Hi Dave,

Sorry, put the graph in the header section

Rachael

 

[Oldsoftboss]

Have come up with this code from the Microsoft Graph example Db:

Me![graphorders].Object.Application.Chart.axes(2).minimumscale = CDbl(Me![minscale]) 'Set the minimum scale
Me![graphorders].Object.Application.Chart.axes(2).maximumscale = CDbl(Me![maxscale]) 'Set the maximum scale
Me![graphorders].Object.Application.Chart.axes(2).minorunit = CDbl(Me![minorunit]) 'Set the minor unit
Me![graphorders].Object.Application.Chart.axes(2).majorunit = CDbl(Me![majorunit]) 'Set the major unit

This works great for setting the vertical scale, but I cant get it to limit the horizontal (Date) scale.

Any suggestions ?

Dave

 

[mdcory]

Are you using a query for the data or straight from the table? If you are using a query set the max date that way.

 

[Rachael]

Hi Dave,

Dunno mate but here is exactly what i've done and it works to limit the x axis date scale-

I've got a form with unbound fields for the user to enter a min date and a max date, then a command button that opens the report with the graph on it.

The report has the graph (and everything else) in the header section. In the reports on-activate event I've got this code to control 3 graphs that have transparent backgrounds and all overlay each other but display different data to compare things like irrigation amount applied, rainfall, and soil moisture levels over the set period of time. It was a bit tedious getting them to line up but seems to work well. This is my code (2 events) exactly pasted into here-

Option Compare Database
Option Explicit

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Me.Graph1.Requery
Me.Graph8.Requery
Me.Graph9.Requery
End Sub

Private Sub Report_Activate()
Dim objChart1 As Graph.chart
Dim objChart2 As Graph.chart
Dim objChart3 As Graph.chart

Dim objAxis1 As Object
Dim objAxis2 As Object
Dim objAxis3 As Object

Set objChart1 = Me.Graph1.Object
Set objChart2 = Me.Graph8.Object
Set objChart3 = Me.Graph9.Object

Set objAxis1 = objChart1.axes(xlCategory)
Set objAxis2 = objChart2.axes(xlCategory)
Set objAxis3 = objChart3.axes(xlCategory)

If objAxis1.Type = 1 Then
objAxis1.minimumscale = (Forms!MoistIrrigChart![Date1])
objAxis1.maximumscale = (Forms!MoistIrrigChart![Date2])
End If

If objAxis2.Type = 1 Then
objAxis2.minimumscale = (Forms!MoistIrrigChart![Date1])
objAxis2.maximumscale = (Forms!MoistIrrigChart![Date2])
End If

If objAxis3.Type = 1 Then
objAxis3.minimumscale = (Forms!MoistIrrigChart![Date1])
objAxis3.maximumscale = (Forms!MoistIrrigChart![Date2])
End If

End Sub

Hope this helps, it does work for me.

Rachael

 

[Oldsoftboss]

Thank Racheal, will give it a go and get back to you.

 

[Oldsoftboss]

Still struggling


Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

Me.Graph70.Requery

End Sub
..................................................................................

Private Sub Report_Activate()

Dim objChart1 As Graph.chart
Dim objAxis1 As Object

Set objChart1 = Me.Graph70.Object
Set objAxis1 = objChart1.axes(xlCategory)

If objAxis1.Type = 1 Then
objAxis1.minimumscale = (Forms!frmRepairReports![txtStart])
objAxis1.maximumscale = (Forms!frmRepairReports![txtEnd])
End If

End Sub

Now bringing up the attached error

 

[Oldsoftboss]

Dunno mate.

Maaate

The report has the graph (and everything else) in the header section.

Tried the Report Header, tried the Page Header, same error.


Thanks for the perseverance.

Dave

 

[Rachael]

Dave,

Is your fields on the form set to dates, I don't know if this has any bearing on things but my unbound fields are set to medium dates. I can't see why this code is not working, have you tried importing to a new database and trying, I remeber this all being very fickle and sometimes I didn't even feel as though I changed anything but would stop working so I'd revert to a saved copy.

Have you referenced Graph 9.0 object library?

Rachael

 

[tmd_63]

Hi Rachael,

How much work have you done on charts?

I have been having problems with line graphs and I am looking for details on the graph object's properties.

I have now got a graph that will display four values on a time-line type graph but I need to enable/disable each of the values. I know it can be done by selecting the graph object and double clicking the column headers in the graph datasheet, but I need this done in vba code instead.

Any ideas?

 

[teel73]

Access and Excel use the same facility - MS Graph. Your problem is that the Access "Chart Wizard" is dumber than a box of rocks. The Excel "Chart Wizard" KNOWS that it is dumber than a box of rocks - and doesn't make so many incorrect assumptions. I guess 'cause the Access wizard has delusions of grandeur?

If you know how or learn how to use VBA and then link the Graph library to your module references, you can then control the properties of your charts directly from VBA, never mind the gross stupidity of the chart wizard. You MIGHT have to link the MS Graph references and then use Object Browser on a Chart Object to determine its properties. But it is possible to make it work OK.

And BTW, MS Graph is not a bad charting tool. I've pushed the heck out of it more than once. In a U.S. Government office (where I work), MS Graph meets all data charting requirements. All of them. Third-party tools are used ONLY for things that are diagrammatic, like Org charts or process flow charts or the like.

Hi Doc_Man,

I'm joining this thread a little late I hope you will be able to help me. I am pretty proficient with VBA and I know exactly what you're talking about when you say add the reference. I am trying to do exactly what you all were discussing which is control my graph through code. I have a pie chart with 4 categories but if one of the categories has no data then it doesn't show and the colors in my Legend Key are shifted. I would like to control that through code. Can you help me please. My graph is named "Graph1" on a form named "frmDisplayValues". I don't know how to get the following code to work:

Graph1.Legend.LegendEntries(1) _
    .LegendKey.MarkerBackgroundColorIndex = 5

That code returns this error "Unable to set the MarkerBackgroundColorIndex property of the LegendKey class.

I don't know what I should be doing. Do I need to declare a variable as a graph or chart or something. Please help. I think I'm on the right track.