Dynamically Change RowSource of Chart Object

[Philly44]

I have report that contains a chart on it. What I need to do is make this chart completely customizable, thats part not so bad I have pretty much gotten most of that done. What I need some help is how do I change the RowSource of the chart through VBA???
I have it setup now that it will dynamicaly create a string that I am using as a UNION query, this seems to work fine. THe problem I have is that it won't let me change the RowSource of my Chart to this string.
I tried this:
Me!Chart.RowSourceType = "Table/Query"
Me!Chart.RowSource = ChartSQL

Chart -> the name of my object
ChartSQl-> the string that contains the UNION query

Any help would be greatly appreciated

FYI: I am using Acc97

Thanks

 

[CosmoKramer]

Try adding:

Me!Chart.Requery

after your RowSource statement....

 

[Philly44]

Sorry I should have further stated the problem. I get an error when I try Me!Graph.RowSource = ChartSQL, it tells me "You have an invlaid reference to the RowSource Property.

Any help would be really appreciated on this

 

[CosmoKramer]

How about

Me!Chart.[b]RecordSource[/b] = ChartSQL

 

[Philly44]

CosmoKramer: WHen I try that I get "Object Doesn't Support This Property or Method"

I have on eother idea but I'm not sure if its possible, here goes:

Is it possible to rewrite a query dynamically. I think if I can do that than I'll just the RowSource Property in Design view to an empty query and then dynamically rewrite it???

Something like qryTemplate = ChartSQL (I have tried this code and it does not work as is)

If anyone can figure this out I'll be in your debt

 

[CosmoKramer]

OK, How about:

Me!Chart.RowSourceType = "Table/Query"
Me!Chart.RowSource = "ChartSQL"
                     ^
                     |
---------------------- Quotes around ChartSQL

 

[Philly44]

Nope I have tried that too and I get the first error, Just using RowSourceType = "Table/Query" will give an error

Thanx anyways

 

[RLB2]

Folks, I am also having the same problem.. Has anyone figured this out. I have checked all my VBA/Access books and can't seem to find an anser to it.

Do I need to declare (Dim) anything before referencing the

Report!ReportName!ChartName.RowSource = NewSQL String ???

Any help is much appreciated!

Rob

 

[POKY]

Hi

is the event in "Format Section"?

there it must be!

poky

 

[RLB2]

Poky,

The Event is in the DATA section of the Properties for the Chart in the report. I hope that is what you are looking for?


Rob

 

[POKY]

Hi Rob

in "Detail" section (of report) choose "on format" event. i think its must work.

good luck

 

[zyabc]

Philly 44, have u found the solution as to how to change the rowsource dynamically. Pls help me out

 

[AlanDI1]

I don't know if you guys are still looking for a solution to changing the RowSource on a chart but I was having similar problems and found a solution. I was getting the "invalid reference" error (I think 2455). However, when I went into debug and stepped through it worked.

I have my code in the report OPEN event because you cannot change this property after a report is open. The other thing I found is that it is a timing thing. So if you put a pause of some kind before the attempt to change the rowsource it works.

My Code is:
' you can find a wait routine - try this post thread705-672928
WaitFor (1)
Me.<chartName>.RowSource = <variable or string with desired rowsource>

That works fine.

Good Luck

 

[HockeyGuy]

I found that a chart behaved like a subreport when trying to change the recordsource of the object dynamically. I got around this by binding the chart to a query called &quot;qryTemp&quot;. I would then change the SQL behind the qryTemp and not need to touch the report/chart.

Example:

'rewrite the SQL of the object 'qryTemp' to reflect the new data source for the subreport

strSQL = &quot;SELECT blah, blah...;&quot;
Set db = CurrentDb()
db.QueryDefs.Delete &quot;qryTemp&quot;
Set qry = db.CreateQueryDef(&quot;qryTemp&quot;, strSQL)
Set db = Nothing
Set qry = Nothing

Note: You don't need to delete the &quot;qryTemp&quot;. You could simply edit the SQL properties of the query. If you do delete the query temp then you need to trap for error 3265 in case there is no query called &quot;qryTemp&quot;

Just call me &quot;catskinner&quot;

HTH

Rick

 

[pdldavis]

Hi, this works for me. I'm setting the charts row source based on a frame selection. I place the code in the BEFORE Update event of the frame:

Private Sub frmChartType_BeforeUpdate(Cancel As Integer)
Dim stsql As String

Select Case Me.frmChartType

Case Is = 1


stsql = &quot;SELECT tblQuality.Type, Sum(tblQuality.ReworkCost) AS SumOfReworkCost, Sum(tblQuality.Mhrs) &quot; & _
&quot;AS SumOfMhrs FROM tblQuality GROUP BY tblQuality.Type, tblQuality.Month, tblQuality.staffID &quot; & _
&quot;HAVING (((tblQuality.Type)=[Forms]![frmGraphtest]![txtType]) AND ((tblQuality.Month)=[Forms]![frmGraphtest]![txtmonth])); &quot;

Me.oleChart.RowSourceType = &quot;Table/Query&quot;
oleChart.Object.Application.PlotBy = 1

Case Is = 2


stsql = &quot;SELECT tblQuality.CauseReason, Count(tblQuality.CauseReason) AS Totals FROM tblQuality &quot; & _
&quot;GROUP BY tblQuality.CauseReason, tblQuality.Month, tblQuality.Type &quot; & _
&quot;HAVING (((tblQuality.Month)=Forms!frmGraphtest!txtMonth) And ((tblQuality.Type)=Forms!frmGraphtest!txtType)); &quot;

Me.oleChart.RowSourceType = &quot;Table/Query&quot;
oleChart.Object.Application.PlotBy = 2

End Select

Me.oleChart.RowSource = stsql

End Sub

Hope that helps.