Trouble Populating Graph Object Via VBA

[(PeteCresswell)]

I'm in MS Access VBA, populating an MS Graph 11 object on an Access form.

Bottom like is that .DataSheet gets loaded if I pause the code and then resume,
but does not get loaded otherwise.

Stepping through the code, it's like the Graph.Datasheet is going
"Poof!" unless I pause the code on or after line 3822 and then resume.

After an unsuccessful load when I right-click the form's Chart object and select
'Edit' I can see that the datasheet is empty - even though the code to load
it has run without throwing an error and ChartTitle.Text shows the value I
assigned to it.

Been Google-ing for three days; went down to Barns & Noble last nite in hopes of
finding a book on MS Graph... Nada.

This smells like some little quirk in MS Graph - or a major case of RCI by Yours
Truly.

One person said he had experienced the same thing, but fixed it by setting focus
on the offending control before the code loaded .DataSheet. I tried doing that
but was not successful.


The Offending Code:
===================================================
3800 If okToProceed = True Then
3810 Set chartRS = curDB().OpenRecordset("qryChart", dbOpenSnapshot,
dbForwardOnly)
3811 With chartRS
3812 If ((.BOF = True) And (.EOF = True)) Then
3813 bugAlert True, "Unexpected empty recordset"
3814 End If
3819 End With

3821 Set fChart = Chart_Open(myTitle)
' -----------------------------------------------------------------------
' As long as I pause the code here, .DataSheet gets loaded.
' Anywhere before this and it does not.

3822 Set myGraphApp = fChart!gphStats.Object.Application
' -----------------------------------------------------------------------
3823 Set myDataSheet = myGraphApp.DataSheet
3829 Set myChart = myGraphApp.Chart

3840 With myDataSheet
3841 .Cells.Delete
3842 .Cells.ClearContents
3843 .Cells(1, 1) = "Date"
3849 .Cells(1, 2) = myColumnLabel_Value

3850 r = 2
3851 Do Until chartRS.EOF = True
3852 .Cells(r, 1).NumberFormat = "mm-yy"
3853 .Cells(r, 1) = Month(chartRS!StatDate) & "-" &
Year(chartRS!StatDate)
3854 .Cells(r, 2) = Format$(chartRS!StatValue, "#0.000")
3855 chartRS.MoveNext
3856 r = r + 1
3857 Loop
3859 End With

3860 With myChart
3861 .HasTitle = True
3862 .ChartTitle.Text = myTitle
3863 .ChartTitle.Font.Size = 8
3869 .ChartTitle.Font.Bold = False

3870 .Axes(xlValue).HasTitle = False
3871 .Axes(xlValue).TickLabels.NumberFormat = "#0.000"
3872 .Axes(xlValue).TickLabels.Font.Bold = False
3879 .Axes(xlValue).TickLabels.Font.Size = 8

3880 .Axes(xlCategory).HasTitle = False
3881 .Axes(xlCategory).TickLabels.NumberFormat = "mm-dd"
3882 .Axes(xlCategory).TickLabels.Font.Bold = False
3889 .Axes(xlCategory).MinorTickMark = xlTickMarkInside
3897 End With
3998 End If
3999 DoCmd.Hourglass False
===================================================

 

[Peter Martin]

Pete,

Don't know what Chart_Open() does (.Activate?) but why don't you just set
the fChart!gphStats.Rowsource to qryChart, and issue a requery on it when
necessary? (or "SELECT Format([StatDate],'mmm-yy') AS Date,
Format([StatValue],"0.000") AS myColumnLabel_Value FROM qryChart"). That
gets rid of 3800-3859 (empy rs will show blank graph), remaining property
sets only needed if you're changing them at runtime.
Your working like an Excel programmer - relax, this is access.

HTH- Peter

 

[(PeteCresswell)]

Per Peter Martin:

Don't know what Chart_Open() does (.Activate?) but why don't you just set
the fChart!gphStats.Rowsource to qryChart, and issue a requery on it when
necessary? (or "SELECT Format([StatDate],'mmm-yy') AS Date,
Format([StatValue],"0.000") AS myColumnLabel_Value FROM qryChart"). That
gets rid of 3800-3859 (empy rs will show blank graph), remaining property
sets only needed if you're changing them at runtime.
Your working like an Excel programmer - relax, this is access.

Chart_Open() opens new instances of frmChart. Users want tb able to create an
unlimited number of charts as they click on various statistics.

No .RowSource query because the computatins behind each type of chart are very
complex/sequential in nature - i.e. I have to create a work table.

But now that you've said it, I guess all the charts could share the same work
table just by adding .hWnd as a column in the table and modifying the query in
Form_Open().

The basic issue, however, persists even when carving away all the multiple form
instance stuff - i.e. even when the form just opens and populates it's own
..DataSheet.


I have found a workaround, however. Instead of populating .DataSheet, I build
a semicolon-delimites string and just feed it directly to the OLE object's
..RowSource.

To modify chart properties, however (i.e. TickMarks, Fonts, and so-forth) by
going do the .Chart object, I still have to go through the motions of populating
..DataSheet by building a single row - no matter that the row doesn't seem to
conflict with what I feed to .RowSource.

This makes me think that maybe I'm trying to fool Mother Nature by going at the
Graph.Application objects instead of going to the OLE object on the form.
OTOH, there's got tb a limit on the size of the string that I push into
..RowSource - but that's moot for now bc I the most datapoints I will need for
this app is 120.

There are probably props available on the form's OLE object to do everything I
want TickMark and ChartTitleText-wise...but what I have works and I'm going with
it for now.

Can't find much by Googling. Nothing in Barns & Noble. Geeze, this really
does seem tb kind of a black art...