Desperate for MS Graph alternative

[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.