Create an MSGraph chart object (1 Viewer)

jjmclell · Aug 11, 2009

Ok, if anyone can figure this one out, they're a genius. What I need to know is... HOW DO YOU PROGRAMMATICALLY CREATE A CHART IN ACCESS?????????? I've been bashing my head over this one for days and I know I'm close but I'm obviously missing something. Here's what I've been trying:

Code:

DoCmd.OpenReport "chartsReport", acViewDesign
Application.CreateReportControl "chartsReport", acObjectFrame, acDetail
Reports("chartsReport").Controls("OLEUnbound1").Class = "MSGraph.Chart.8"

And that's where I'm hung up. Setting the class of my objectframe doesn't do anything and the property doesn't even stick after I set it. If anyone can tell me how to take an empty unbound object frame and stick an MSGraph chart object into it, like I said, you're a genius (and I would greatly appreciate it).

Thanks!

jjmclell

Endre · Aug 11, 2009

Try the following:

Code:

Function CreateGraph()
 
    DoCmd.OpenReport "ChartsReport", acViewDesign
 
    Dim ctrl_count As Integer
    ctrl_count = Reports("ChartsReport").Controls.Count
 
    Application.CreateReportControl "ChartsReport", acObjectFrame, acDetail
    Reports("ChartsReport").Controls(ctrl_count).Class = "MSGraph.Chart.8"
    
    DoCmd.Save acReport, "ChartsReport"
    DoCmd.Close acReport, "ChartsReport"
 
End Function

jjmclell · Aug 11, 2009

Adding the save and close commands didn't change anything...the code still just created an empty object frame. What I need is an object frame of class MSGraph.Chart.8 with the properties associated with a chart object (most importantly the RowSource property). Even though we're setting the class property of the object frame, it's not doing anything; furthermore, if you look at the properties of the object frame that we create with this code, the class property is blank, even though we set it.

ChrisO · Aug 12, 2009

G’day jjmclell.

I can’t answer your question directly but would like to make an observation if I may.

This could easily turn into a lot of work only to be foiled in the ‘eleventh hour’.

If the thing you wish to create relies on going into design view it will not work if you ever want to produce and MDE file of your application.

On the other hand, if what you really want to do is set the RowSource of the chart in the Report then there are ways.

I’ve attached a demo of manipulating charts on a Form and then using that to open two Reports.

The demo is in Access97 but does work up to 2007.

Regards,
Chris.

Endre · Aug 12, 2009

So......there is a small "trick" involved. Once we create the object we need to set the OLEData to that of a valid Microsoft.Graph object. We can store that in a table, so...

Create a table with a single field set to OLE Object.
Open up the form in view and add a single record (Right Click > Insert Object).
Set the object as: Create New > Microsoft Graph Chart.
Then Close Graph down.

You should have a single graph object entry in your table which we will use in our recordset as in code below.

I have used [cw_tblChartTemplates].[ChartObject] as Table and fieldname. The reason I have used this is that this table exists as is in the C:\Program Files\Microsoft Office\OFFICE11\ACWZUSR.MDT database. You can set your app to link to this table if you don't want to create a new table and the following code will run just fine:

Code:

Function cg()
 
    DoCmd.OpenReport "ChartsReport", acViewDesign
 
    Dim ctrl_count As Integer
    ctrl_count = Reports("ChartsReport").Controls.Count
 
    Application.CreateReportControl "ChartsReport", acObjectFrame, acDetail
 
    Dim rs_OLETab As Recordset
    Set rs_OLETab = CurrentDb.OpenRecordset("SELECT * FROM cw_tblChartTemplates;")
    rs_OLETab.MoveFirst
    ' Set the OleData property to that of a Graph (can use OLE field from Table in this case)
    Reports("ChartsReport").Controls(ctrl_count).OleData = rs_OLETab!ChartObject
 
    DoCmd.Save acReport, "ChartsReport"
    DoCmd.Close acReport, "ChartsReport"
 
End Function

Endre · Aug 12, 2009

Hey Chris - that's a really cool interface

. I really like the slider "control" you have created - it's given me some new ideas on how to handle / display larger volumes of data. Do you mind if I create a thread hyperlinking forum users to it? Or have you done this a long time ago?

The problem (as I understand it) that jj has, is that he is creating a virgin report from VBA, and has no reference to objects that don't even exist in his collections yet. His problem when creating the graph object (as an example) is that the object doesn't even have a rowsource property to even do anything with yet as the control is not even of the correct type.

Setting the OLEData as I suggested turns it into the correct ObjectType, and so he can take it from there.

jjmclell · Aug 12, 2009

Hey Endre,

Great work, you're a genius! I did one thing slightly differently...I just set the OLEData for my report's empty object frame to equal that of the chart object in my form...and lo and behold! It worked! I can't thank you enough. The problem I was having is that I was only looking through the Access VBA reference where there is no mention of the .OLEData property. I just found it in the pure VBA help file...talk about obscure. Anyways, since I'm pretty sure this code doesn't exist anywhere on the net, here's what I did to solve this problem:

Code:

  DoCmd.OpenReport "chartsReport", acViewDesign
 
  Dim ctrl_Count As Integer
  ctrl_Count = Reports("chartsReport").Controls.Count
 
  Application.CreateReportControl "chartsReport", acObjectFrame, acDetail
  Reports("chartsReport").Controls(ctrl_Count).OleData = chartForm.chart1.OleData
 
  DoCmd.Save acReport, "chartsReport"
  DoCmd.Close acReport, "chartsReport"

Thanks again!

jjmclell

ChrisO · Aug 14, 2009

G’day Endre.

Please feel free to use or modify it as you please.

It was written about 3 years ago and has had 1 major overhaul which might be of interest.

Originally I wrote it using a Date/Time field for the SampleDateTime field.
That field then needed to be changed to Double and so the entire demo now calculates on Doubles even though the display is still formatted using regional settings.

The reason for that change might be of interest to you. With the Date/Time field the charts would malfunction when regional settings were set to Afrikaans. I never found out why that is because the failure was internal to the charts. But the scrapping of the Date/Time field and the conversion to pure Doubles fixed the problem.

It’s such and interesting failure that I kept a copy of the old database with the fault.

I’ve attached the faulty database here if you’re interested. If you open the faulty database using US or Australian regional settings it’s okay but switching to Afrikaans causes the charts to retrace for the first thirteen hours.

If you can figure out why that fault occurs then please let me know.

Regards,
Chris.

Endre · Aug 14, 2009

Hi Chris, Will have a look and let you know if I come up with anything.

Why do you say it is such an interesting failure? I think the interface you have developed there to see such a large amount of data with the focus on a small set when required plus the slider feature is remarkable. It shows design creativity in programmers where (in my personal case) there usually is very little.

I am looking for a mechanism to create a "control" that represents the duration and possibly size of an object e.g. a Project. Vertically I would like to see many Projects and horizontally a time scale. But each project is represented by a long rectangle, positioned correctly on the time scale for start and finish. Much like MSProject, but a simple version. However, I would like to be able to "grab" the ends of the rectangle and stretch it's size to change duration, or slide the rectangle horizontally. Here's the catch though - I would like it in a sub-form style to see multiple projects one on top of each other.

I've found doing something like this is OK, but as soon as you move to the next "record" in the sub-form, it's "rectangle" starts taking on properties of the previous record that held the focus. And the objects start jumping all over the place......

Each project will have a cost per time period (which would also be nice to have some way of displaying as the cost changes over the duration of the project), and the graph at the top of the entire form shows total project cost, or total cost of selected objects.

Any idea of how to create such a control that works on a "sub-form"?

ChrisO · Aug 14, 2009

By interesting failure I mean the failure with Afrikaans regional settings as happens in my second upload.

Drag drop and resize can be done on a Form, see attached, but in the long run I think it would probably be better to use MS Project.

Regards,
Chris.

blah · Jan 26, 2014

Just used a modified version of Endre's method and thank you very much!

ChrisO's thing is pretty amazing.

Create an MSGraph chart object (1 Viewer)

jjmclell

Registered User.

Endre

Registered User.

jjmclell

Registered User.

ChrisO

Registered User.

Attachments

Endre

Registered User.

Endre

Registered User.

jjmclell

Registered User.

ChrisO

Registered User.

Attachments

Endre

Registered User.

ChrisO

Registered User.

Attachments

blah

Registered User.

Similar threads

Users who are viewing this thread