I think I've about exhausted my Googling on this issue and so am ready to do the inevitable and start a thread!
I'm programatically creating a graph on a form in Access 2007 using the msgraph 12.0 control and, in general, it's all working fine. On the form the user selects some criteria and can then add that data series to the graph - the idea being they can pick more than one value (it's graphing various performance metrics from servers) and "add" it into the graph as an additional line, so they can compare 2 or more data series over the same period of time.
The problem is, I cannot find any approach down at the Datasheet handling level in VBA that allows me to add another column, or range of cells, into the datasheet without it wiping the existing data that's in there.
I do the first "add" operation and it correctly adds in the cells into the datasheet, updates the graph, presto..
(link removed)
(FFS - I'd love to give you some links to pictures/screenshots but ofcourse being a n00b I can't)
If I now pull a new data set and try to programatically insert it into the datasheet object, there seems to be some sort of implicit "wipe", as the old data is cleared. The new cells appear in the correct position in the datasheet (ie. starting where the previous cells finished), but the ranges of the old data are all now cleared.
(link removed)
No, I'm not calling .clear ;-)
Here's the core bit of code...
I can see via autocompletion and some old 2003 online MSDN documentation that there are various methods such as "insert", which I've played around with, but cannot get to do anything fruitful. The lack of substantial documentation from Microsoft isn't helping, to be honest.
Anyone any ideas?
I'm programatically creating a graph on a form in Access 2007 using the msgraph 12.0 control and, in general, it's all working fine. On the form the user selects some criteria and can then add that data series to the graph - the idea being they can pick more than one value (it's graphing various performance metrics from servers) and "add" it into the graph as an additional line, so they can compare 2 or more data series over the same period of time.
The problem is, I cannot find any approach down at the Datasheet handling level in VBA that allows me to add another column, or range of cells, into the datasheet without it wiping the existing data that's in there.
I do the first "add" operation and it correctly adds in the cells into the datasheet, updates the graph, presto..
(link removed)
(FFS - I'd love to give you some links to pictures/screenshots but ofcourse being a n00b I can't)
If I now pull a new data set and try to programatically insert it into the datasheet object, there seems to be some sort of implicit "wipe", as the old data is cleared. The new cells appear in the correct position in the datasheet (ie. starting where the previous cells finished), but the ranges of the old data are all now cleared.
(link removed)
No, I'm not calling .clear ;-)
Here's the core bit of code...
Code:
Dim myObj As chart
Set myObj = Me.graph2.Object
myObj.Application.DataSheet.Activate
myObj.Application.DataSheet.Cells(1, 1).Value = "Time"
intSeries = intSeries + 1
myObj.Application.DataSheet.Cells(1, intSeries + 1).Value = "Series " & intSeries
Set db = CurrentDb()
Set rst = db.OpenRecordset(strChartSQL, DAO.RecordsetTypeEnum.dbOpenSnapshot)
Dim rowcount As Integer
rowcount = intStartAt
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
'.ChartType = xlLineMarkers
'.HasTitle = False
'.Axes(Graph.XlAxisType.xlValue).HasTitle = True
'.Axes(Graph.XlAxisType.xlCategory) = XlCategoryType.xlTimeScale
' Loop through all the rows in the database, adding them into the chart sheet
Do While Not rst.EOF
With myObj.Application.DataSheet
.Cells(rowcount, 1).Value = rst("CalcTimestamp").Value
.Cells(rowcount, 1 + intSeries).Value = rst("CalcMetric").Value
End With
rowcount = rowcount + 1
rst.MoveNext
Loop
intStartAt = rowcount
Else
'No records in the recordset - nothing to do/add...
End If
myObj.Application.Update
myObj.Application.Quit
Set myObj = Nothing
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Anyone any ideas?
Attachments
Last edited: