I am usually OK with figuring out this stuff, but this one has me stumped after many hours of searching multiple online forums. Hoping someone can point out where I am going wrong.
It’s an Access 2003 (also tried A2010) database with multiple tables, queries, forms, etc. One form has a MSGraph XY graph whose property sheet reports that it’s of type MSGraph.Chart.8. The chart contains four data series that are derived from a table, and they all display correctly on the form.
My next step is to label the actual data points on one of the series with some textual information that is also in the data table. I’ve seen numerous examples of doing this in Excel, and I am trying to apply the same logic to the graphic control on the form. Here is the core of the offending VBA code. I’ve omitted many details for brevity, and commented-out the offending lines. RS is a valid recordset, and chtProfile is the graphic control on the form.
Do While Not (rs.EOF)
If Not IsNull(rs!waypoint) Then ‘ found a label point
With chtProfile.Object.Application.Chart.seriescollection(4)
MsgBox .points.Count & vbCrLf & i & vbCrLf & .points(i).hasdatalabel & vbCrLf & rs!waypoint ‘ this works as expected
‘.points(i).hasdatalabel = True ‘ this works OK
‘.points(i).applydatalabels Type:=2 ‘ this works OK, the Y-value appears on the chart when this line is executed
‘.points(i).datalabel.Text = rs!waypoint ‘ this causes error message: cannot set text property
End With
End If
rs.MoveNext
i = i + 1
Loop
The error occurs when I try to assign a value to the datalabel.text property, yet this is exactly the syntax used in all the Excel examples I’ve seen. I am guessing that the VBA graphic control in Access operates differently than the “same” control in Excel, but that’s just speculation. If so, what is the correct syntax or property for the graphic control?
On a more general note, is there a way to view all the valid properties of a Chart object from within VBA? I’ve tried walking thru the Object Browser for the chtProfile object, but can’t find anything useful. IntelliSense does not work on this object.
Thanks for any help.
