Show OLE Excel Chart using text field for SourceDoc's file path

[Evi]

I'm using Access 2000
I want to insert a different excel chart into a report for each client in my
database.

To simplify
My Client Table, TblClient, contains a text field called SpreadPath which
contains the file path to each chart
eg
E:\DDocs\AccDBs\Chart1.xls
for Client1

The chart is the only item on the first page of the workbook.

I grouped my report by ClientID
I put my chart (ole1) into the ClientID header by clicking on an Unbound
Object Frame and choosing Excel Chart.
This gives me a default picture of a chart.
In ole1's Properties I have
OLE Type Allowed: Embedded (I actually wanted Linked but it tells me that
this is a read-only property so maybe that's just for Forms)
OLE Type Allowed: Either
I tried putting Chart Area into the Source Item and I tried leaving that out
(no difference)

In the On Format Event's Code Page for the Client ID Header I've put
Me.ole1.SourceDoc = Me.SpreadPath

There is a microsoft article on doing this with a form but most of the
things don't work in a report, causing an error message (ie you would not
Enable a chart in a report)

No error messages are caused when I Debug or Preview the Report but the
chart still just shows its default diagram and not my spreadsheet's chart.
Have I misunderstood or omitted something?

Evi

 

[Evi]

I seem to be fated to ask questions that don't have an answer
Any ideas anyone?
Evi

 

[Stephen Lebans]

Hi Evi,
there have always been issues using Unbound OLE Frame controls on an Access
Report. The simplest solution is to use an OLE Frame control bound directly
to a field containing the embedded Chart object. If that is not possible,
then an alternative is to use an Unbound OLE Frame control on a hidden form.
Update the OLE control on the form during the format event of the Detail
section, then copy the VALUE prop of the Frame control on the Form to that
of the report. This method does not always work but depends on the OLE
Server app.

The core issue is that many OLE Server apps do not update the contents of
the Frame control properly under the Access report writer's OLE Host
interface. That's why so many control's will work on an Access form but fail
on a report, or work with a Bound OLE Frame control but not an Unbound one.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Evi]

Thanks Stephen, At least I know it's not personal
I suppose, since the only thing the chart is 'bound to' is the File Path
field, I can't make it a bound Object Control because those have to be
embedded in a table - with all the ensuing bloat.

I tried using SourcDoc in a form's On current to load the chart. Scarily
slow even if I go into Design view.


I guess I could turn the chart into an Image and use the Image Frame (that,
at least, works) but then goodbye any chance of updating the chart
I guess the solution is, for most part, to either take the bloat or use
Access's own chart program


Evi