By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Chart with SQL source

P: n/a
I wish to create a report with a chart, but I want to use a SQL
statement as the source of the data, using VBA code in the reports load
event

I found the following snippet

Dim objGraph As Object, objDS As Object, rsData As Recordset

Set objGraph = Me!Graph1.Object
Set objDS = objGraph.Application.DataSheet
Set rsData = CurrentDb.OpenRecordset("Select * from
YourTargetDataSource")

however, when stepping through the code, the objGraph ends up with the
value Nothing, so I end up without any chart data.

How to I create a chart, where I can programmatically add data and
series to the chart?

Jul 14 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On 14 Jul 2006 00:31:00 -0700, dk****@memco.co.uk wrote:
>I wish to create a report with a chart, but I want to use a SQL
statement as the source of the data, using VBA code in the reports load
event

I found the following snippet

Dim objGraph As Object, objDS As Object, rsData As Recordset

Set objGraph = Me!Graph1.Object
Set objDS = objGraph.Application.DataSheet
Set rsData = CurrentDb.OpenRecordset("Select * from
YourTargetDataSource")

however, when stepping through the code, the objGraph ends up with the
value Nothing, so I end up without any chart data.

How to I create a chart, where I can programmatically add data and
series to the chart?
I'm not sure I dare answer any more questions about charts.

1. Have you got the chart control showing in your references? You specimen code worke for me.
But ...

2. To do what you want you just need to have

Graph1.rowsource="...." where ... is your desired SQL.
In a report in the load event - in a form it can be anywhere, eg on a button, since the chart will
be redrawn when the rowsource changes.

Jul 14 '06 #2

P: n/a
On Fri, 14 Jul 2006 15:32:49 GMT, polite person <sn**@snippers.comwrote:
>On 14 Jul 2006 00:31:00 -0700, dk****@memco.co.uk wrote:
>>I wish to create a report with a chart, but I want to use a SQL
statement as the source of the data, using VBA code in the reports load
event

I found the following snippet

Dim objGraph As Object, objDS As Object, rsData As Recordset

Set objGraph = Me!Graph1.Object
Set objDS = objGraph.Application.DataSheet
Set rsData = CurrentDb.OpenRecordset("Select * from
YourTargetDataSource")

however, when stepping through the code, the objGraph ends up with the
value Nothing, so I end up without any chart data.

How to I create a chart, where I can programmatically add data and
series to the chart?

I'm not sure I dare answer any more questions about charts.

1. Have you got the chart control showing in your references? You specimen code worke for me.
But ...

2. To do what you want you just need to have

Graph1.rowsource="...." where ... is your desired SQL.
In a report in the load event - in a form it can be anywhere, eg on a button, since the chart will
be redrawn when the rowsource changes.
PS if it is in a report you would normally set the SQL in the report design, i the rowsource
property of the chart object, setting rowsourcetype as table/query
Jul 14 '06 #3

P: n/a
Thanks for the reply.

I haven't done any advanced Access for about 8 years now, so I am a bit
rusty...

The suggested method of Graph1.RowSource looks like the ideal method,
but I don't have the RowSource method auto-suggestion.

You mention ensuring the references have been added, but how is this
performed? Everything is all in the same database, so the specific
report reference shows up in the properties.

Jul 17 '06 #4

P: n/a
On 17 Jul 2006 02:51:04 -0700, dk****@memco.co.uk wrote:
>Thanks for the reply.

I haven't done any advanced Access for about 8 years now, so I am a bit
rusty...

The suggested method of Graph1.RowSource looks like the ideal method,
but I don't have the RowSource method auto-suggestion.

You mention ensuring the references have been added, but how is this
performed? Everything is all in the same database, so the specific
report reference shows up in the properties.
For references, open a code window, eg a module in design view. I am using a97 here, and references
are on the tools menu. You will have several items checked, one of which should be the Microsoft
Chart control. If it isn't, browse to find MSCHRT**.OCX and select it (prob in windows/system32)

For rowsource, open the form (or report) in design view, open the properties window and click on the
chart control on the form. Rowsourcetype and rowsourceare in the data properties.
Jul 17 '06 #5

P: n/a
Ok, starting to get harder...

I have a2002 developers edition, a2003, office 2007 beta installed.

This app is being developed using access 2003.

I don't have any msch*.ocx on my system.

In the references, I can add a reference to Microsoft Graph 12.0 Object
library, which I think points to ...\Office12\Graph.exe - the label
doesn't fit on the references form to see the whole string!

Even if I add this reference, I still don't get any extra methods or
properties to the Graph1 object.
>From the reports designer, the graph is a MSGraph.Chart.8, but so far I
can't find where the reference for this is.

Jul 17 '06 #6

P: n/a
Getting a little closer...

The Access 2000 and lateruse an ActiveX chart instead of the earlier
ocx, despite the fact that when you add the chart, it uses the Chart8
object.

If manually embedding a Microsoft Office Chart 11.0 I get all of the
additional properties, however there is no property for the SQL source.

Anyone familiar with using SQL source with these later controls?

I want to use SQL, as I am dynamically adding more than 1 series to the
chart.

Jul 17 '06 #7

P: n/a
On 17 Jul 2006 08:12:38 -0700, "DaveKelly" <dk****@memco.co.ukwrote:
>Getting a little closer...

The Access 2000 and lateruse an ActiveX chart instead of the earlier
ocx, despite the fact that when you add the chart, it uses the Chart8
object.

If manually embedding a Microsoft Office Chart 11.0 I get all of the
additional properties, however there is no property for the SQL source.

Anyone familiar with using SQL source with these later controls?

I want to use SQL, as I am dynamically adding more than 1 series to the
chart.
Isn't there a property called datasource? If not on the properties sheet look in the object browser.
I'm afraid I haven't later versions of Access at hand here.

Jul 17 '06 #8

P: n/a
DaveKelly wrote:
Anyone familiar with using SQL source with these later controls?

I want to use SQL, as I am dynamically adding more than 1 series to the
chart.
The latter requirement might make it tough, but what I've done
successfully for graph objects that have no series (value and breakdown
only) or one series is to simply create the chart from a querydef
object, say qryGraph. Then create SQL and do soemthing like (partial code):

dim qdf as DAO.querydef

set qdf = currentb.querydefs("qryGraph")

qdf.sql = strSQL 'SQL generated from whatever proc.

I'm probably not telling you anything you haven't tried, but thought I'd
throw it in there, just in case. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jul 17 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.