Set Rowsource property for Chart Control using VB code

[ge liu]

Hi,
Could anyone tell me how to using visual basic code to set
the rowsource for a chart control? I tried to use this
type of statement:

me.controls("graph3").rowsource="select Jan03, Feb03,
March03 from sales_table"

But it does not work. When I run it in the Report_Open
event, it gives an error message saying that rowsource is
not a valid property. When I run it in the Detail_Format,
or Detail_Print events, it gives me a different error
message: "Run-time error 2191, You can't set the RowSource
Property after printing has started."

I WAS able to set the Rowsource property manually from the
property box. But I need to automate this using visual
Basic code, and in the Access Online Help, it says
rowsource CAN be set with Visual Basic. I wonder what is
the matter here.

Deos anyone know how to get around this?

Thanks a million!


Ge

 

[SA]

Ge:

There's really no way to set the darn row source at run time unless you go
into design view. However, there are some workarounds that you can do.

1.) Set your Chart's row source to be that of a temporary table then use an
append query to set the data you want in the chart in the table before the
report is run.

2.) Try putting the data from the recordsource into the chart object's data
sheet at run time (in the on print event of the report something like:

Dim objGraph as object
Dim objDataSheet as Object
Set objGraph = Me!MyChart.Object
Set objDataSheet = objGraph.Application.DataSheet

Then, using XL type notation for ranges loop through your RS and set the
values into the datasheet. A pain but it would problably work.

 

[William Grigg]

Steve,

I must be misunderstanding something in your response. I execute the
following code at run time all the time:

gphObject.rowSource = "blah blah"

where gphObject is a chart object on a form.

Bill

 

[ge liu]

Thanks for your kind response, Steve and William.

William, could you post your code which you indicated in
the email? or send it to my email address:
(e-mail address removed). Here is my code which does NOT
work. THis is the Detail_Formart event of a report, in
which the chart (Graph3) is a chart control on the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim i As Integer
Dim fldTemp As Field
Dim Ord_BeginMonth As Integer
Dim ord_EndMonth As Integer


Dim dbsForecast As Database
Dim qdf As QueryDef
Dim strSelect As String

Set dbsForecast = CurrentDb
Set qdf = dbsForecast.QueryDefs("RowsourceQuery")

With qdf

For Each fldTemp In .Fields
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo2, 1) = 0 Then
Ord_BeginMonth = fldTemp.OrdinalPosition
End If
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo3, 1) = 0 Then
ord_EndMonth = fldTemp.OrdinalPosition
End If

Next fldTemp

strSelect = "SELECT " & QueryName & ".engrLevel"

For i = Ord_BeginMonth To ord_EndMonth
strSelect = strSelect & "," & QueryName & "."
& (.Fields(i).name)
Next i

strSelect = strSelect & " FROM " & QueryName
strSelect = strSelect & " WHERE " & QueryName
& ".engrType='" & etName & "'"

End With

Me.Graph3.RowSource = strSelect

dbsForecast.Close
End Sub

Best Regards,

GE

 

[Duane Hookom]

Nice code. Couldn't you use;
Set qdfs = db.QueryDefs
If queryExists("qryVoltages") Then
qdfs("qryVoltages").sql =Me![Definition]
End If
GraphObject.Requery


--
Duane Hookom
MS Access MVP

Ge,

Steve is correct. I noticed that what I do for reports is to have the graph
object RowSource set to a query. Then at runtime I delete the query, and
rebuild it, and then requery the graph object. That is similar to Steve's
suggestion of the Append query. Below is some code:

Set qdfs = db.QueryDefs
If queryExists("qryVoltages") Then
qdfs.Delete ("qryVoltages")
End If
Set qdf = db.CreateQueryDef("qryVoltages", Me![Definition])
GraphObject.Requery

Bill

Thanks for your kind response, Steve and William.

William, could you post your code which you indicated in
the email? or send it to my email address:
(e-mail address removed). Here is my code which does NOT
work. THis is the Detail_Formart event of a report, in
which the chart (Graph3) is a chart control on the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim i As Integer
Dim fldTemp As Field
Dim Ord_BeginMonth As Integer
Dim ord_EndMonth As Integer


Dim dbsForecast As Database
Dim qdf As QueryDef
Dim strSelect As String

Set dbsForecast = CurrentDb
Set qdf = dbsForecast.QueryDefs("RowsourceQuery")

With qdf

For Each fldTemp In .Fields
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo2, 1) = 0 Then
Ord_BeginMonth = fldTemp.OrdinalPosition
End If
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo3, 1) = 0 Then
ord_EndMonth = fldTemp.OrdinalPosition
End If

Next fldTemp

strSelect = "SELECT " & QueryName & ".engrLevel"

For i = Ord_BeginMonth To ord_EndMonth
strSelect = strSelect & "," & QueryName & "."
& (.Fields(i).name)
Next i

strSelect = strSelect & " FROM " & QueryName
strSelect = strSelect & " WHERE " & QueryName
& ".engrType='" & etName & "'"

End With

Me.Graph3.RowSource = strSelect

dbsForecast.Close
End Sub

Best Regards,

GE

 

[William Grigg]

Thanks Duane. That worked just fine and is obviously better code.

Bill

Nice code. Couldn't you use;
Set qdfs = db.QueryDefs
If queryExists("qryVoltages") Then
qdfs("qryVoltages").sql =Me![Definition]
End If
GraphObject.Requery


--
Duane Hookom
MS Access MVP

Ge,

Steve is correct. I noticed that what I do for reports is to have the graph
object RowSource set to a query. Then at runtime I delete the query, and
rebuild it, and then requery the graph object. That is similar to Steve's
suggestion of the Append query. Below is some code:

Set qdfs = db.QueryDefs
If queryExists("qryVoltages") Then
qdfs.Delete ("qryVoltages")
End If
Set qdf = db.CreateQueryDef("qryVoltages", Me![Definition])
GraphObject.Requery

Bill

Thanks for your kind response, Steve and William.

William, could you post your code which you indicated in
the email? or send it to my email address:
(e-mail address removed). Here is my code which does NOT
work. THis is the Detail_Formart event of a report, in
which the chart (Graph3) is a chart control on the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim i As Integer
Dim fldTemp As Field
Dim Ord_BeginMonth As Integer
Dim ord_EndMonth As Integer


Dim dbsForecast As Database
Dim qdf As QueryDef
Dim strSelect As String

Set dbsForecast = CurrentDb
Set qdf = dbsForecast.QueryDefs("RowsourceQuery")

With qdf

For Each fldTemp In .Fields
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo2, 1) = 0 Then
Ord_BeginMonth = fldTemp.OrdinalPosition
End If
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo3, 1) = 0 Then
ord_EndMonth = fldTemp.OrdinalPosition
End If

Next fldTemp

strSelect = "SELECT " & QueryName & ".engrLevel"

For i = Ord_BeginMonth To ord_EndMonth
strSelect = strSelect & "," & QueryName & "."
& (.Fields(i).name)
Next i

strSelect = strSelect & " FROM " & QueryName
strSelect = strSelect & " WHERE " & QueryName
& ".engrType='" & etName & "'"

End With

Me.Graph3.RowSource = strSelect

dbsForecast.Close
End Sub

Best Regards,

GE

-----Original Message-----
Steve,

I must be misunderstanding something in your response. I
execute the
following code at run time all the time:

gphObject.rowSource = "blah blah"

where gphObject is a chart object on a form.

Bill

Ge:

There's really no way to set the darn row source at run
time unless you go
into design view. However, there are some workarounds
that you can do.

1.) Set your Chart's row source to be that of a
temporary table then use
an
append query to set the data you want in the chart in
the table before the
report is run.

2.) Try putting the data from the recordsource into the
chart object's
data
sheet at run time (in the on print event of the report
something like:

Dim objGraph as object
Dim objDataSheet as Object
Set objGraph = Me!MyChart.Object
Set objDataSheet = objGraph.Application.DataSheet

Then, using XL type notation for ranges loop through
your RS and set the
values into the datasheet. A pain but it would
problably work.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


Hi,
Could anyone tell me how to using visual basic code
to set
the rowsource for a chart control? I tried to use
this
type of statement:

me.controls("graph3").rowsource="select Jan03, Feb03,
March03 from sales_table"

But it does not work. When I run it in the
Report_Open
event, it gives an error message saying that
rowsource is
not a valid property. When I run it in the
Detail_Format,
or Detail_Print events, it gives me a different error
message: "Run-time error 2191, You can't set the
RowSource
Property after printing has started."

I WAS able to set the Rowsource property manually
from the
property box. But I need to automate this using
visual
Basic code, and in the Access Online Help, it says
rowsource CAN be set with Visual Basic. I wonder
what is
the matter here.

Deos anyone know how to get around this?

Thanks a million!


Ge





.

 

[ge.liu]

Thanks, Wiliiam for the explaination. I don't know why
reports have this strange restriction. I feel like this
is a "bug" to be fixed by Microsoft.

Ge

 

[ge liu]

Thanks, Bill and Duane, for the answere, I will try get
around this with yours and steve's sugestions.

Best Regards,
Ge

-----Original Message-----
Thanks Duane. That worked just fine and is obviously better code.

Bill

Nice code. Couldn't you use;
Set qdfs = db.QueryDefs
If queryExists("qryVoltages") Then
qdfs("qryVoltages").sql =Me! [Definition]
End If
GraphObject.Requery


--
Duane Hookom
MS Access MVP

Ge,

Steve is correct. I noticed that what I do for

reports is to have the
graph

object RowSource set to a query. Then at runtime I delete the query, and
rebuild it, and then requery the graph object. That

is similar to
Steve's

suggestion of the Append query. Below is some code:

Set qdfs = db.QueryDefs
If queryExists("qryVoltages") Then
qdfs.Delete ("qryVoltages")
End If
Set qdf = db.CreateQueryDef

("qryVoltages",
Me![Definition])

GraphObject.Requery

Bill


Thanks for your kind response, Steve and William.

William, could you post your code which you indicated in
the email? or send it to my email address:
(e-mail address removed). Here is my code which does NOT
work. THis is the Detail_Formart event of a report, in
which the chart (Graph3) is a chart control on the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim i As Integer
Dim fldTemp As Field
Dim Ord_BeginMonth As Integer
Dim ord_EndMonth As Integer


Dim dbsForecast As Database
Dim qdf As QueryDef
Dim strSelect As String

Set dbsForecast = CurrentDb
Set qdf = dbsForecast.QueryDefs ("RowsourceQuery")

With qdf

For Each fldTemp In .Fields
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo2, 1) = 0 Then
Ord_BeginMonth = fldTemp.OrdinalPosition
End If
If StrComp(fldTemp.name, Forms!
FmChseMth_charts!Combo3, 1) = 0 Then
ord_EndMonth = fldTemp.OrdinalPosition
End If

Next fldTemp

strSelect = "SELECT " & QueryName & ".engrLevel"

For i = Ord_BeginMonth To ord_EndMonth
strSelect = strSelect & "," & QueryName & "."
& (.Fields(i).name)
Next i

strSelect = strSelect & " FROM " & QueryName
strSelect = strSelect & " WHERE " & QueryName
& ".engrType='" & etName & "'"

End With

Me.Graph3.RowSource = strSelect

dbsForecast.Close
End Sub

Best Regards,

GE

-----Original Message-----
Steve,

I must be misunderstanding something in your response. I
execute the
following code at run time all the time:

gphObject.rowSource = "blah blah"

where gphObject is a chart object on a form.

Bill

Ge:

There's really no way to set the darn row source at run
time unless you go
into design view. However, there are some workarounds
that you can do.

1.) Set your Chart's row source to be that of a
temporary table then use
an
append query to set the data you want in the chart in
the table before the
report is run.

2.) Try putting the data from the recordsource into the
chart object's
data
sheet at run time (in the on print event of the report
something like:

Dim objGraph as object
Dim objDataSheet as Object
Set objGraph = Me!MyChart.Object
Set objDataSheet = objGraph.Application.DataSheet

Then, using XL type notation for ranges loop through
your RS and set the
values into the datasheet. A pain but it would
problably work.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


Hi,
Could anyone tell me how to using visual basic code
to set
the rowsource for a chart control? I tried to use
this
type of statement:

me.controls("graph3").rowsource="select Jan03, Feb03,
March03 from sales_table"

But it does not work. When I run it in the
Report_Open
event, it gives an error message saying that
rowsource is
not a valid property. When I run it in the
Detail_Format,
or Detail_Print events, it gives me a different error
message: "Run-time error 2191, You can't set the
RowSource
Property after printing has started."

I WAS able to set the Rowsource property manually
from the
property box. But I need to automate this using
visual
Basic code, and in the Access Online Help, it says
rowsource CAN be set with Visual Basic. I wonder
what is
the matter here.

Deos anyone know how to get around this?

Thanks a million!


Ge





.

.