report with Graph Ole object, can I manipulate the datasource of the graph by code?

  • Thread starter Thread starter Philip Leduc
  • Start date Start date
P

Philip Leduc

This may sound like a dumb question but how do I:
Using Access I know that you can for example in the "on open" event change
the recordsource of a report by another query or SQL statement,

However when you build a graph based on a table (in my cae a crosstab query)
the datasource is not a property of the report but from the ole object in
the report
(this for example what is in the row souce of the ole object:
TRANSFORM Sum(Qry7000_AllExpensesUSD.[amount$]) AS [SumOfamount$] SELECT
Qry7000_AllExpensesUSD.period FROM Qry7000_AllExpensesUSD WHERE
(((Qry7000_AllExpensesUSD.[category private])="7111AA")) GROUP BY
Qry7000_AllExpensesUSD.period PIVOT Qry7000_AllExpensesUSD.[category
private];)

How to a refer to this property from the report open?
me.oleName......?????

am I on the wrong track, I want to programmatically be able to change the
category private code let say from 7111AA to 3600AA????

Thanks for the help,

Philip
 
Philip:

You can not directly access or address the data source or row source for a
graph in a report. You have to wrk with the Datasheet object of report's
graph object and load the recordset's data into the data sheet and the
refresh the graph.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

---------------------------------

Here's an example, you can do this in the On Activate or in the On Print
event of the report section that contains the graph.

Dim objGraph As Object, objDS As Object, rsData As Recordset
Dim intRowMax%, intColMax%, arrData As Variant
Dim i%, j%
Set objGraph = Me!Graph1.Object
'get the datasheet object
Set objDS = objGraph.Application.DataSheet
'open the target recordset
Set rsData = CurrentDb.OpenRecordset("Select * from tblABC")
'load the data into an array using GetRows, this reads the data once and is
fast
arrData = rsData.GetRows(200)
intRowMax = UBound(arrData, 1)
intColMax = UBound(arrData, 2)
'data sheet cells are in row, column, starting at 1,1 with the headers like
Excel
'array is in column, row starting at 0, where row 0 is the first Row of
data; no headers
'Add the column heads using the field names of the recordset
objDS.cells.Clear
For i = 0 To rsData.Fields.Count - 1
objDS.cells(1, i + 1) = rsData.Fields(i).Name
Next i
'now add the data from the recordset
For i = 0 To intRowMax
For j = 0 To intColMax
objDS.cells(i + 2, j + 1) = arrData(j, i)
Next j
Next i
Set objDS = Nothing
DoEvents
objGraph.Refresh
'optional to save the dataset in the graph's datasheet, so you'll see it in
design mode
'Me.Graph1.Object.Application.Update
Set objGraph = Nothing

End Sub
 
Back
Top