Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 08-11-2009, 11:12 AM   #1
jjmclell
Newly Registered User
 
Join Date: Aug 2009
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
jjmclell is on a distinguished road
Question Create an MSGraph chart object

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

jjmclell is offline   Reply With Quote
Old 08-11-2009, 11:50 AM   #2
Endre
Registered User
 
Join Date: Jul 2009
Location: Johannesburg, South Africa
Posts: 137
Thanks: 0
Thanked 6 Times in 4 Posts
Endre is on a distinguished road
Smile Re: Create an MSGraph chart object

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
Endre is offline   Reply With Quote
Old 08-11-2009, 01:13 PM   #3
jjmclell
Newly Registered User
 
Join Date: Aug 2009
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
jjmclell is on a distinguished road
Question Re: Create an MSGraph chart object

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.

jjmclell is offline   Reply With Quote
Old 08-12-2009, 12:45 AM   #4
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 274 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
Re: Create an MSGraph chart object

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.
Attached Files
File Type: zip KWHoursForPostingA97.zip (267.4 KB, 834 views)
__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ChrisO is offline   Reply With Quote
Old 08-12-2009, 01:18 AM   #5
Endre
Registered User
 
Join Date: Jul 2009
Location: Johannesburg, South Africa
Posts: 137
Thanks: 0
Thanked 6 Times in 4 Posts
Endre is on a distinguished road
Smile Re: Create an MSGraph chart object

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 is offline   Reply With Quote
Old 08-12-2009, 02:11 AM   #6
Endre
Registered User
 
Join Date: Jul 2009
Location: Johannesburg, South Africa
Posts: 137
Thanks: 0
Thanked 6 Times in 4 Posts
Endre is on a distinguished road
Smile Re: Create an MSGraph chart object

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.
Endre is offline   Reply With Quote
Old 08-12-2009, 05:11 AM   #7
jjmclell
Newly Registered User
 
Join Date: Aug 2009
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
jjmclell is on a distinguished road
Lightbulb Re: Create an MSGraph chart object

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

jjmclell is offline   Reply With Quote
Old 08-13-2009, 04:39 PM   #8
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 274 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
Re: Create an MSGraph chart object

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.
Attached Files
File Type: zip Faulty_KWHours97.zip (248.3 KB, 365 views)
__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ChrisO is offline   Reply With Quote
Old 08-13-2009, 09:18 PM   #9
Endre
Registered User
 
Join Date: Jul 2009
Location: Johannesburg, South Africa
Posts: 137
Thanks: 0
Thanked 6 Times in 4 Posts
Endre is on a distinguished road
Smile Re: Create an MSGraph chart object

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"?
Endre is offline   Reply With Quote
Old 08-13-2009, 10:08 PM   #10
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 274 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
Re: Create an MSGraph chart object

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.
Attached Files
File Type: zip DragAndDropA97.zip (74.6 KB, 434 views)
__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ChrisO is offline   Reply With Quote
Old 01-26-2014, 07:03 AM   #11
blah
Newly Registered User
 
Join Date: Jun 2011
Posts: 20
Thanks: 8
Thanked 0 Times in 0 Posts
blah is on a distinguished road
Re: Create an MSGraph chart object

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

ChrisO's thing is pretty amazing.

__________________
the machines never forget
blah is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
vba to create and retrieve a chart from within Access bobmac- Modules & VBA 0 05-13-2008 10:26 PM
[SOLVED] Object Library Not registered/ActiveX Component Can't Create Object Paul Langham General 1 10-12-2006 06:09 AM
Pivoting Chart Items with VBA Andy L Excel 4 07-05-2006 01:14 AM
Can't add chart to Data Access Page, 'Missing Object' Meltdown General 0 11-23-2002 04:31 PM
ActiveX Component Can't Create Object sharonbl Forms 1 10-13-2000 07:01 PM




All times are GMT -8. The time now is 02:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World