Question How to access the MSGraph properties

[tmd_63]

I have a line graph that displays date-time categories for four values using four different lines. I also have two calender controls to change the start and end display days to reduce the data displayed and this all works fine.
I need to change the major units for the X-Axis using code so that the major units are displayed for a small time period and not a jumbled mess when displaying a large time period (the data is an electricity useage in half-hour intervals over several months/years).
I also need to include/exclude the lines programmatically via four tick boxes.
How do I access the X-Axis majorunit property and the include property?. Axes(2) only gets the Primary Y-Axis and that is the only one available.

 

[KenHigg]

The best way to do this is to make sure you have MS Graph set up in your references then use the object browser to examine the Graph library. Highlighting an entry and hitting F1 will bring up the MS Help for that topic.

 

[tmd_63]

I have tried that but it still doesn't help with trying to get the majorunit to change on the x-axis.
Here is what I have so far (which changes the primary Y axis instead. all other scales are inaccessable).
Private Sub lstXAxis_Change()
If Me![lstXAxis].Value = "12Hr" Then
Me![Graph1].Axes(2).MajorUnit = 24
ElseIf Me![lstXAxis].Value = "24Hr" Then
Me![Graph1].Axes(2).MajorUnit = 48
ElseIf Me![lstXAxis].Value = "7Day" Then
Me![Graph1].Axes(2).MajorUnit = 336
ElseIf Me![lstXAxis].Value = "14Day" Then
Me![Graph1].Axes(2).MajorUnit = 672
ElseIf Me![lstXAxis].Value = "21Day" Then
Me![Graph1].Axes(2).MajorUnit = 1008
ElseIf Me![lstXAxis].Value = "28Day" Then
Me![Graph1].Axes(2).MajorUnit = 1344
End If
End Sub

 

[KenHigg]

I assume you tried changing Axes(2) to Axes(1)?

 

[tmd_63]

Run-time error '1004'
Unable to set the MajorUnit property of the Axis class

 

[KenHigg]

Hum... That has me stumped. I'm not real good with charts, maybe someone else can jump in

 

[Len Boorman]

Just a thought

After grabbing start and end dates calculate teh number of days (date2-date1) and then use an if statement to fire of 1 of say 4 identical (except for X axis) charts.

Maybe not the slickest/smartest/cleverest but it might work

Len

 

[tmd_63]

Len, The x-axis works fine as far as the data is concerned.
It is just that the axis is currently set with the major axis setting of 24 (once per 12 hours of data). This is fine if the user is viewing a week worth of data, but if they are viewing a months worth, the major mark (ie the dates and times) are so close together that it is impossible to read. I need to reset the period between the major marks depending on the start and end dates.

 

[Len Boorman]

Okay I understand what you want to do but at the moment neither you nor others have been able to offer a solution. My thought was that a work around would be to configure say 4 separate charts that were basically the same, with the same source but the major axis setting would be different. Then using the select parameters may a decision as to which would be the most appropriate format to use.

I know its not a fix for your original question but it could be a solution out of a hole as a temporary solution while you seek a better/more apt solution.

Len

 

[KenHigg]

I have a chart on a form where I completely change its underlying query with a combo box and the chart does all of it on the fly.

 

[tmd_63]

Solved part of it.
The problem of the X-Axis not being set to a value was due to the time/date axis being a category instead of a time axis. I changed the code from Axis(1).MajorUnit to Axis(1).TickLabelSpacing and it now works beautifully.
I still have a problem with the actual data.
If you double click the chart object, then double click the header for a dataset, you can include/exclude it from the display. I need to know how to do this in VBA code instead?

 

[Rabbie]

Since both Excel and Access use MS graph, you can record an Excel macro in VBA doing what you want and then copy/paste the VBA to your Access program.

 

[tmd_63]

YAHOOOOOO! Solved it.

The problem was that I needed to preset some variables first. Here is the code for one of the check boxes.

Private Sub chkKVARH_Click()
Dim GraphObj As Graph.Chart
Dim objGrp As Graph.Application
Dim objDS As Graph.DataSheet
Dim objCht As Graph.Chart
Dim Test As Integer

Set GraphObj = Forms!Energy!Graph1.Object 'Must set the variable for the graph first
Set objGrp = GraphObj.Application 'Then reference the application
Set objDS = objGrp.DataSheet 'And finally reference the datasheet

If Me.chkKWH Or Me.chkPF Or Me.chkTotal Then 'Check to ensure at least one other dataset is visible

If Me.chkKVARH Then 'If the check box is true then include data in graph
'KVARH column is number 2 in the datasheet

With objDS
.Columns(2).Include = True
End With

Else 'If the check box is false then remove the data from graph

With objDS
.Columns(2).Include = False
End With

End If

Else

Test = MsgBox("Can Not Change unless a Second Dataset is Visible!", vbOKOnly, "Error")
If Me.chkKVARH Then 'If this is the last dataset then reset the checkbox to it original value
Me.chkKVARH.Value = False
Else
Me.chkKVARH.Value = True
End If
Exit Sub

End If
End Sub

 

[Rabbie]

Glad to hear you have solved it. Also good that you posted your solution.