MsChart Example

[jgoodman00]

Can anybody recommend any links to examples of how to use the MsChart control?

The MSDN help seems to be rubbish.

Basically, I have an ado datagrid object, bound to an ado datacontrol, which has three columns.
SessionDate
LapNo
LapTime

I need to construct a line chart, with LapNo on the x-axis, LapTime on the y-axis, & the data grouped by SessionDate (i.e. each session date has is plotted as a seperate line.




Any suggestions?
James Goodman MCP

http://www.james-goodman.co.uk

 

[jgoodman00]

I have now done this, by placing the relevant data into an array as follows:

Sub DrawChart(lngSessionID As Long)
Dim arrLapTimes()
Dim i As Integer
Dim sngMinY As Single, sngMaxY As Single
Dim strLapNo As String

'Open the recordset
strSQL = "SELECT * FROM qryLapTimes WHERE SessionID = " & lngSessionID
rst.Open (strSQL), cnn, adOpenStatic

'Proportion the array
ReDim arrLapTimes(1 To rst.RecordCount, 1 To 2)

'Fill the array
For i = 1 To rst.RecordCount
strLapNo = "Lap " & rst.Fields("LapNo&quot
arrLapTimes(i, 1) = strLapNo
arrLapTimes(i, 2) = rst.Fields("LapTime&quot
rst.MoveNext
Next i

'Configure the y-axis
sngMinY = (Me.rstSessionStatistics.Recordset.Fields("MinLapTime&quot * 0.95)
sngMaxY = (Me.rstSessionStatistics.Recordset.Fields("MaxLapTime&quot * 1.05)
Me.MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Minimum = sngMinY
Me.MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Maximum = sngMaxY
Me.MSChart1.Plot.Axis(VtChAxisIdY).AxisTitle = "Lap Time /secs"

'Configure the x-axis
Me.MSChart1.Plot.Axis(VtChAxisIdX).AxisTitle = "Lap No"
Me.MSChart1.Plot.Axis(VtChAxisIdX).ValueScale.Minimum = 1
Me.MSChart1.Plot.Axis(VtChAxisIdX).ValueScale.Maximum = rst.RecordCount

'Draw the chart
Me.MSChart1.ChartData = arrLapTimes

'Close the recordset
rst.Close


End Sub


I ended up not referencing the ado data control, because navigation within it (to fill the array) caused problems with other subs which run as the user navigates through the data grid.


Hopefully, if anybody else gets this problem they will be able to use this . James Goodman MCP

http://www.james-goodman.co.uk

 

[maegan]

hi James,

i went through your code as i am doing something similar to yours. i'm not familiar at all with MSChart.. hope you can help me out here.

(Me.rstSessionStatistics.Recordset.Fields("MinLapTime&quot * 0.95)

what's rstSessionStatistics used for? cause when i do 'Me.' in my vb, there's no rstSessionStatistics option available.
and why multiply with 0.95?

Please help! thanks in advance.

 

[jgoodman00]

rstSessionStatistics is an ado data control I have used on my form, which contains certain data from my data source. The reason I used this value & then multiplied by 0.95 was to setup the min value on the y-axis.

e.g. If my minimum laptime was 100seconds, my minimum value on the y-axis would be 95seconds.

Just to get it working, it might be worth you commenting out the blocks:
'Configure the y-axis
sngMinY = (Me.rstSessionStatistics.Recordset.Fields("MinLapTime&quot * 0.95)
sngMaxY = (Me.rstSessionStatistics.Recordset.Fields("MaxLapTime&quot * 1.05)
Me.MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Minimum = sngMinY
Me.MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Maximum = sngMaxY
Me.MSChart1.Plot.Axis(VtChAxisIdY).AxisTitle = "Lap Time /secs"

'Configure the x-axis
Me.MSChart1.Plot.Axis(VtChAxisIdX).AxisTitle = "Lap No"
Me.MSChart1.Plot.Axis(VtChAxisIdX).ValueScale.Minimum = 1
Me.MSChart1.Plot.Axis(VtChAxisIdX).ValueScale.Maximum = rst.RecordCount


Let me know if this helps...

James Goodman MCP

http://www.james-goodman.co.uk

 

[maegan]

thanks a lot for the reply! appreciate it a lot. yeah.. think i understand better now. there's another thing i want to ask. for your chart, the strLapNo is the label on the x-axis, am i right? i've tried something but the label doesn't seem to display.

arrMonth(mth, 1) = "Month " & mth

and my x-axis title doesn't appear as well! whereas there's no problem displaying the title for my y-axis.

MSChart1.Plot.Axis(VtChAxisIdX).AxisTitle = "Month"

here's the complete code :

Private Sub monthlyReport()

MSChart1.Title.Text = "Total Materials Borrowed"

For mth = aFrom To aTo

strSQL = "SELECT * FROM qryCirculation WHERE Month(DateLoan) = '" & mth & "'"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
arrMonth(mth, 1) = "Month " & mth
arrMonth(mth, 2) = rst.RecordCount

rst.Close

Next mth

MSChart1.Plot.Axis(VtChAxisIdY).AxisTitle = "No. Of Books"
MSChart1.Plot.Axis(VtChAxisIdX).AxisTitle = "Month"

MSChart1.ChartData = arrMonth

End Sub

 

[maegan]

i try to set my chart back to defaults (mschart1.ToDefaults) and the labels at the bottom actually appeared! but other things such as the chart title and colours doesn't come out the way i want it to anymore (all have changed to the default settings). sigh.. this is giving me headaches.

 

[jgoodman00]

What is arrMonth?
Where do you declare it?
Where do you dimension it?

Your code is repeatedly opening & closing a recordset. It is also using adLockOptimistic.
Both of these are going to give big performance hits. Why did you do it this way?

If you use the In word on your SQL statement, you should be able to open a single recordset & work through the records. This should boost performance significantly...

James Goodman MCP

http://www.james-goodman.co.uk

 

[maegan]

the reason why i kept on opening and closing the recordset is because i want to run different sql statements each time i go into the loop.. i couldn't think of another way..

Private Sub monthlyReport()

dateFrom = "1/" & aFrom & "/" & yrFrom
dateTo = "1/" & aTo & "/" & yrTo

numberOfMonths = DateDiff("m", dateFrom, dateTo)

ReDim arrMonth(1 To numberOfMonths + 1, 1 to numberOfMonths + 1)

MSChart1.Title.Text = chartTitle

For mth = 1 To numberOfMonths + 1

strMonth = Month(dateFrom)
strYear = Year(dateFrom)

condition = "Month" & con1 & "='" & strMonth & "' AND Year" & con1 & "='" & strYear & "'"
strSQL = "SELECT * FROM " & tbl & " WHERE " & condition
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

arrMonth(mth,1) = "Month " & mth
arrMonth(mth,2) = rst.RecordCount

sngMaxY = rst.RecordCount + 1
MSChart1.Plot.Axis(VtChAxisIdY).ValueScale.Maximum = sngMaxY

dateFrom = DateAdd("m", 1, dateFrom)

rst.Close

Next mth

MSChart1.Plot.Axis(VtChAxisIdY).AxisTitle = "No. Of Books"
MSChart1.Plot.Axis(VtChAxisIdX).AxisTitle = "Month"

MSChart1.ChartData = arrMonth


the code may look a bit messy, i know..sorry for that. but my x-axis title and my x-axis label still doesn't display. do you have any idea why? thanks.

 

[jgoodman00]

It looks like you are graphing the number of occurences of something within a given timeframe. Am I correct?

If so, using the Count function in your SQL statement. This should then give you a single list of items with a count value against them (& thus remove the need for opening/closing the recordset).

Have you looked through the object properties for the graph control? I suspect there is an option to turn them on/off. Perhaps you have inadvertantly disabled the labels.

You could also try deleting the current graph control, & insterting a new one. Assign the above code to it & see if the labels appear...

James Goodman MCP

http://www.james-goodman.co.uk

 

[maegan]

you were right. i didn't enable the x-axis properties.. and all along i thought that i must have done something wrong in my code. now the labels are all coming out..

and for the Count function, i think i'm gonna try to use it instead of opening and closing my recordset. thanks a lot!