Set Rowsource property for Chart Control using VB code

  • Thread starter Thread starter ge liu
  • Start date Start date
G

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
 
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,

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
 
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
 
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


William Grigg said:
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


ge liu said:
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
Click to expand...
Click to expand...
 
Thanks Duane. That worked just fine and is obviously better code.

Bill

Duane Hookom said:
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


William Grigg said:
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


ge liu said:
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





.
Click to expand...
Click to expand...
Click to expand...
 
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
 
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

Duane Hookom said:
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


William Grigg said:
Ge,

Steve is correct. I noticed that what I do for
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
("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





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top