Rowsource on Report Chart?

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

Hi Folks bee struggling with this one for a while. I have a chart on a
report for which I need to set the rowsource. I do the same on a form for
the same chart with total success, but this fails on the report. I have
listed the abridged code below, I get an error (2455) saying that
'Expression has Invalid Reference' or Rowsource is not applicable to the
Graph1 chart, or else the rowsource is simply not populated and the default
chart data is shown. Any comment welcome - cheers
================================================================
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
' Comments :
' Parameters :
' Created : 26-04-05 09:29:55 irjc
' Modified :
'
' --------------------------------------------------------
On Error GoTo Sub_ERR_Report_Open
'================================================
'Set recordsource to the sql for the particular scenario
Dim dbCurr As Database

Dim strRecSource1 As String ' Rowsource for each Graph
' in form that uses
Scenario

Dim varScenario As Variant ' The Scenario Number current

Set dbCurr = CurrentDb()

varScenario = DLookup("[ss_selected_scenario]", "system_settings")

Select Case varScenario 'Choose the correct recordsource for the
Current Scenario

Case 1 '====================
'SQL For Scenario 1 all Graphs
strRecSource1 = "SELECT analysis_codes.ad_description AS
[Analysis Code], Sum(budget_schedules.bs_time)" _
& " AS [Man Hours] FROM (qryFabricCondition
LEFT JOIN analysis_codes" _
& " ON qryFabricCondition.ad_analysis_code =
analysis_codes.ad_analysis_code)" _
& " LEFT JOIN budget_schedules ON
qryFabricCondition.fc_id = budget_schedules.fc_fabric_key" _
& " GROUP BY analysis_codes.ad_description,
qryFabricCondition.ad_analysis_code,
qryFabricCondition.fc_program_year" _
& " HAVING
(((qryFabricCondition.fc_program_year)=1)) ORDER BY
qryFabricCondition.ad_analysis_code:"

Me.Label0.Caption = "Scenario 1"
'MsgBox "Case 1 Selected"

Case 2 '====================
Case 3 '==================== 'etc.

End Select

Me!Graph1.RowSource = strRecSource1
Me!Graph1.Requery


Exit Sub

Sub_ERR_Report_Open:
MsgBox "Error number " & Err.Number & ": " & Err.description

End Sub
============================================================================
 
I would set the Row Source of the chart to a saved query. Then use your code
to set the SQL property of the saved query. Also, your SQL syntax ends with
a colon rather than a semi-colon.
 
I had the same problem a few month ago and the only solution I found was
to point my graph object to a query and modify the query instead, BEFORE
effectively call the report.

I ran some tests and they worked, but I had never finalised the work (my
client changed his mind about graphics).

Mauricio Silva - 2005
-> For more information: run a search in this group for "Mauricio Silva graph"
 
Thanks Guys - I figured the Persistent Query was the way to go.

One more question though - Can this code (setting the query SQL) be run from
the Open Report procedure or does it need to be fired before even raising
the report?

--
Kahuna
------------
Mauricio Silva said:
I had the same problem a few month ago and the only solution I found was
to point my graph object to a query and modify the query instead, BEFORE
effectively call the report.

I ran some tests and they worked, but I had never finalised the work (my
client changed his mind about graphics).

Mauricio Silva - 2005
-> For more information: run a search in this group for "Mauricio Silva
graph"

Kahuna said:
Hi Folks bee struggling with this one for a while. I have a chart on a
report for which I need to set the rowsource. I do the same on a form for
the same chart with total success, but this fails on the report. I have
listed the abridged code below, I get an error (2455) saying that
'Expression has Invalid Reference' or Rowsource is not applicable to the
Graph1 chart, or else the rowsource is simply not populated and the
default
chart data is shown. Any comment welcome - cheers
================================================================
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
' Comments :
' Parameters :
' Created : 26-04-05 09:29:55 irjc
' Modified :
'
' --------------------------------------------------------
On Error GoTo Sub_ERR_Report_Open
'================================================
'Set recordsource to the sql for the particular scenario
Dim dbCurr As Database

Dim strRecSource1 As String ' Rowsource for each Graph
' in form that uses
Scenario

Dim varScenario As Variant ' The Scenario Number current

Set dbCurr = CurrentDb()

varScenario = DLookup("[ss_selected_scenario]", "system_settings")

Select Case varScenario 'Choose the correct recordsource for the
Current Scenario

Case 1 '====================
'SQL For Scenario 1 all Graphs
strRecSource1 = "SELECT analysis_codes.ad_description AS
[Analysis Code], Sum(budget_schedules.bs_time)" _
& " AS [Man Hours] FROM
(qryFabricCondition
LEFT JOIN analysis_codes" _
& " ON
qryFabricCondition.ad_analysis_code =
analysis_codes.ad_analysis_code)" _
& " LEFT JOIN budget_schedules ON
qryFabricCondition.fc_id = budget_schedules.fc_fabric_key" _
& " GROUP BY
analysis_codes.ad_description,
qryFabricCondition.ad_analysis_code,
qryFabricCondition.fc_program_year" _
& " HAVING
(((qryFabricCondition.fc_program_year)=1)) ORDER BY
qryFabricCondition.ad_analysis_code:"

Me.Label0.Caption = "Scenario 1"
'MsgBox "Case 1 Selected"

Case 2 '====================
Case 3 '==================== 'etc.

End Select

Me!Graph1.RowSource = strRecSource1
Me!Graph1.Requery


Exit Sub

Sub_ERR_Report_Open:
MsgBox "Error number " & Err.Number & ": " & Err.description

End Sub
============================================================================
Click to expand...
Click to expand...
 
Try and report back :-)
I can't recall my testing of this.

--
Duane Hookom
MS Access MVP
--

Kahuna said:
Thanks Guys - I figured the Persistent Query was the way to go.

One more question though - Can this code (setting the query SQL) be run
from the Open Report procedure or does it need to be fired before even
raising the report?

--
Kahuna
------------
Mauricio Silva said:
I had the same problem a few month ago and the only solution I found was
to point my graph object to a query and modify the query instead, BEFORE
effectively call the report.

I ran some tests and they worked, but I had never finalised the work (my
client changed his mind about graphics).

Mauricio Silva - 2005
-> For more information: run a search in this group for "Mauricio Silva
graph"

Kahuna said:
Hi Folks bee struggling with this one for a while. I have a chart on a
report for which I need to set the rowsource. I do the same on a form
for
the same chart with total success, but this fails on the report. I have
listed the abridged code below, I get an error (2455) saying that
'Expression has Invalid Reference' or Rowsource is not applicable to the
Graph1 chart, or else the rowsource is simply not populated and the
default
chart data is shown. Any comment welcome - cheers
================================================================
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
' Comments :
' Parameters :
' Created : 26-04-05 09:29:55 irjc
' Modified :
'
' --------------------------------------------------------
On Error GoTo Sub_ERR_Report_Open
'================================================
'Set recordsource to the sql for the particular scenario
Dim dbCurr As Database

Dim strRecSource1 As String ' Rowsource for each Graph
' in form that uses
Scenario

Dim varScenario As Variant ' The Scenario Number current

Set dbCurr = CurrentDb()

varScenario = DLookup("[ss_selected_scenario]", "system_settings")

Select Case varScenario 'Choose the correct recordsource for the
Current Scenario

Case 1 '====================
'SQL For Scenario 1 all Graphs
strRecSource1 = "SELECT analysis_codes.ad_description AS
[Analysis Code], Sum(budget_schedules.bs_time)" _
& " AS [Man Hours] FROM
(qryFabricCondition
LEFT JOIN analysis_codes" _
& " ON
qryFabricCondition.ad_analysis_code =
analysis_codes.ad_analysis_code)" _
& " LEFT JOIN budget_schedules ON
qryFabricCondition.fc_id = budget_schedules.fc_fabric_key" _
& " GROUP BY
analysis_codes.ad_description,
qryFabricCondition.ad_analysis_code,
qryFabricCondition.fc_program_year" _
& " HAVING
(((qryFabricCondition.fc_program_year)=1)) ORDER BY
qryFabricCondition.ad_analysis_code:"

Me.Label0.Caption = "Scenario 1"
'MsgBox "Case 1 Selected"

Case 2 '====================
Case 3 '==================== 'etc.

End Select

Me!Graph1.RowSource = strRecSource1
Me!Graph1.Requery


Exit Sub

Sub_ERR_Report_Open:
MsgBox "Error number " & Err.Number & ": " & Err.description

End Sub
============================================================================
Click to expand...
Click to expand...
Click to expand...
 
Duane, thanks for the support. I have the routine working now but I have
another problem. Here's where I am at:

I have a form on which is a chart, there is a button on the form which opens
a report (the one I was setting the RowSource for). The form uses rowsource
via VBA to get the data it needs, and the same sql is then used to populate
a persistent query.

This all works well, but I have a very weird challenge:

On clicking the report button the report opens correctly - displaying the
same data as on the form, but, irregularly, maybe 10% or the time, if I
click the report button - then close the report and open it again, the
record source the report is using appears to have gone back to the original
Report Query (used in the Rowsource).

I suspect the sql used to populate the query on the fly is not being
retained or saved as the query def, and occasionally the report is opened
before the querydef is updated with the sql! Could that be right?

Is there a way save the query after the sql has been applied and before I
open the form? My code is getting pretty long and complex now so I wont post
anymore unless asked.

Appreciate the help Duane

--
Kahuna
------------
Duane Hookom said:
Try and report back :-)
I can't recall my testing of this.

--
Duane Hookom
MS Access MVP
--

Kahuna said:
Thanks Guys - I figured the Persistent Query was the way to go.

One more question though - Can this code (setting the query SQL) be run
from the Open Report procedure or does it need to be fired before even
raising the report?

--
Kahuna
------------
Mauricio Silva said:
I had the same problem a few month ago and the only solution I found was
to point my graph object to a query and modify the query instead, BEFORE
effectively call the report.

I ran some tests and they worked, but I had never finalised the work (my
client changed his mind about graphics).

Mauricio Silva - 2005
-> For more information: run a search in this group for "Mauricio Silva
graph"

:

Hi Folks bee struggling with this one for a while. I have a chart on a
report for which I need to set the rowsource. I do the same on a form
for
the same chart with total success, but this fails on the report. I have
listed the abridged code below, I get an error (2455) saying that
'Expression has Invalid Reference' or Rowsource is not applicable to
the
Graph1 chart, or else the rowsource is simply not populated and the
default
chart data is shown. Any comment welcome - cheers
================================================================
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
' Comments :
' Parameters :
' Created : 26-04-05 09:29:55 irjc
' Modified :
'
' --------------------------------------------------------
On Error GoTo Sub_ERR_Report_Open
'================================================
'Set recordsource to the sql for the particular scenario
Dim dbCurr As Database

Dim strRecSource1 As String ' Rowsource for each Graph
' in form that uses
Scenario

Dim varScenario As Variant ' The Scenario Number current

Set dbCurr = CurrentDb()

varScenario = DLookup("[ss_selected_scenario]", "system_settings")

Select Case varScenario 'Choose the correct recordsource for
the
Current Scenario

Case 1 '====================
'SQL For Scenario 1 all Graphs
strRecSource1 = "SELECT analysis_codes.ad_description
AS
[Analysis Code], Sum(budget_schedules.bs_time)" _
& " AS [Man Hours] FROM
(qryFabricCondition
LEFT JOIN analysis_codes" _
& " ON
qryFabricCondition.ad_analysis_code =
analysis_codes.ad_analysis_code)" _
& " LEFT JOIN budget_schedules ON
qryFabricCondition.fc_id = budget_schedules.fc_fabric_key" _
& " GROUP BY
analysis_codes.ad_description,
qryFabricCondition.ad_analysis_code,
qryFabricCondition.fc_program_year" _
& " HAVING
(((qryFabricCondition.fc_program_year)=1)) ORDER BY
qryFabricCondition.ad_analysis_code:"

Me.Label0.Caption = "Scenario 1"
'MsgBox "Case 1 Selected"

Case 2 '====================
Case 3 '==================== 'etc.

End Select

Me!Graph1.RowSource = strRecSource1
Me!Graph1.Requery


Exit Sub

Sub_ERR_Report_Open:
MsgBox "Error number " & Err.Number & ": " & Err.description

End Sub
============================================================================
Click to expand...
Click to expand...
Click to expand...
 
You might want to look at some fixes at ACGSoft
http://ourworld.compuserve.com/homepages/attac-cg/

--
Duane Hookom
MS Access MVP


Kahuna said:
Duane, thanks for the support. I have the routine working now but I have
another problem. Here's where I am at:

I have a form on which is a chart, there is a button on the form which
opens a report (the one I was setting the RowSource for). The form uses
rowsource via VBA to get the data it needs, and the same sql is then used
to populate a persistent query.

This all works well, but I have a very weird challenge:

On clicking the report button the report opens correctly - displaying the
same data as on the form, but, irregularly, maybe 10% or the time, if I
click the report button - then close the report and open it again, the
record source the report is using appears to have gone back to the
original Report Query (used in the Rowsource).

I suspect the sql used to populate the query on the fly is not being
retained or saved as the query def, and occasionally the report is opened
before the querydef is updated with the sql! Could that be right?

Is there a way save the query after the sql has been applied and before I
open the form? My code is getting pretty long and complex now so I wont
post anymore unless asked.

Appreciate the help Duane

--
Kahuna
------------
Duane Hookom said:
Try and report back :-)
I can't recall my testing of this.

--
Duane Hookom
MS Access MVP
--

Kahuna said:
Thanks Guys - I figured the Persistent Query was the way to go.

One more question though - Can this code (setting the query SQL) be run
from the Open Report procedure or does it need to be fired before even
raising the report?

--
Kahuna
------------
message
I had the same problem a few month ago and the only solution I found
was
to point my graph object to a query and modify the query instead,
BEFORE
effectively call the report.

I ran some tests and they worked, but I had never finalised the work
(my
client changed his mind about graphics).

Mauricio Silva - 2005
-> For more information: run a search in this group for "Mauricio Silva
graph"

:

Hi Folks bee struggling with this one for a while. I have a chart on a
report for which I need to set the rowsource. I do the same on a form
for
the same chart with total success, but this fails on the report. I
have
listed the abridged code below, I get an error (2455) saying that
'Expression has Invalid Reference' or Rowsource is not applicable to
the
Graph1 chart, or else the rowsource is simply not populated and the
default
chart data is shown. Any comment welcome - cheers
================================================================
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
' Comments :
' Parameters :
' Created : 26-04-05 09:29:55 irjc
' Modified :
'
' --------------------------------------------------------
On Error GoTo Sub_ERR_Report_Open
'================================================
'Set recordsource to the sql for the particular scenario
Dim dbCurr As Database

Dim strRecSource1 As String ' Rowsource for each Graph
' in form that uses
Scenario

Dim varScenario As Variant ' The Scenario Number current

Set dbCurr = CurrentDb()

varScenario = DLookup("[ss_selected_scenario]", "system_settings")

Select Case varScenario 'Choose the correct recordsource for
the
Current Scenario

Case 1 '====================
'SQL For Scenario 1 all Graphs
strRecSource1 = "SELECT analysis_codes.ad_description
AS
[Analysis Code], Sum(budget_schedules.bs_time)" _
& " AS [Man Hours] FROM
(qryFabricCondition
LEFT JOIN analysis_codes" _
& " ON
qryFabricCondition.ad_analysis_code =
analysis_codes.ad_analysis_code)" _
& " LEFT JOIN budget_schedules ON
qryFabricCondition.fc_id = budget_schedules.fc_fabric_key" _
& " GROUP BY
analysis_codes.ad_description,
qryFabricCondition.ad_analysis_code,
qryFabricCondition.fc_program_year" _
& " HAVING
(((qryFabricCondition.fc_program_year)=1)) ORDER BY
qryFabricCondition.ad_analysis_code:"

Me.Label0.Caption = "Scenario 1"
'MsgBox "Case 1 Selected"

Case 2 '====================
Case 3 '==================== 'etc.

End Select

Me!Graph1.RowSource = strRecSource1
Me!Graph1.Requery


Exit Sub

Sub_ERR_Report_Open:
MsgBox "Error number " & Err.Number & ": " & Err.description

End Sub
============================================================================
Click to expand...
Click to expand...
Click to expand...
 
Great Stuff Duane - that's exactly what I needed - cheers my friend!

BTW That's a pretty good resource.

--
Kahuna
------------
Duane Hookom said:
You might want to look at some fixes at ACGSoft
http://ourworld.compuserve.com/homepages/attac-cg/

--
Duane Hookom
MS Access MVP


Kahuna said:
Duane, thanks for the support. I have the routine working now but I have
another problem. Here's where I am at:

I have a form on which is a chart, there is a button on the form which
opens a report (the one I was setting the RowSource for). The form uses
rowsource via VBA to get the data it needs, and the same sql is then used
to populate a persistent query.

This all works well, but I have a very weird challenge:

On clicking the report button the report opens correctly - displaying the
same data as on the form, but, irregularly, maybe 10% or the time, if I
click the report button - then close the report and open it again, the
record source the report is using appears to have gone back to the
original Report Query (used in the Rowsource).

I suspect the sql used to populate the query on the fly is not being
retained or saved as the query def, and occasionally the report is opened
before the querydef is updated with the sql! Could that be right?

Is there a way save the query after the sql has been applied and before I
open the form? My code is getting pretty long and complex now so I wont
post anymore unless asked.

Appreciate the help Duane

--
Kahuna
------------
Duane Hookom said:
Try and report back :-)
I can't recall my testing of this.

--
Duane Hookom
MS Access MVP
--

Thanks Guys - I figured the Persistent Query was the way to go.

One more question though - Can this code (setting the query SQL) be run
from the Open Report procedure or does it need to be fired before even
raising the report?

--
Kahuna
------------
message
I had the same problem a few month ago and the only solution I found
was
to point my graph object to a query and modify the query instead,
BEFORE
effectively call the report.

I ran some tests and they worked, but I had never finalised the work
(my
client changed his mind about graphics).

Mauricio Silva - 2005
-> For more information: run a search in this group for "Mauricio
Silva graph"

:

Hi Folks bee struggling with this one for a while. I have a chart on
a
report for which I need to set the rowsource. I do the same on a form
for
the same chart with total success, but this fails on the report. I
have
listed the abridged code below, I get an error (2455) saying that
'Expression has Invalid Reference' or Rowsource is not applicable to
the
Graph1 chart, or else the rowsource is simply not populated and the
default
chart data is shown. Any comment welcome - cheers
================================================================
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
' Comments :
' Parameters :
' Created : 26-04-05 09:29:55 irjc
' Modified :
'
' --------------------------------------------------------
On Error GoTo Sub_ERR_Report_Open
'================================================
'Set recordsource to the sql for the particular scenario
Dim dbCurr As Database

Dim strRecSource1 As String ' Rowsource for each Graph
' in form that
uses
Scenario

Dim varScenario As Variant ' The Scenario Number current

Set dbCurr = CurrentDb()

varScenario = DLookup("[ss_selected_scenario]",
"system_settings")

Select Case varScenario 'Choose the correct recordsource for
the
Current Scenario

Case 1 '====================
'SQL For Scenario 1 all Graphs
strRecSource1 = "SELECT analysis_codes.ad_description
AS
[Analysis Code], Sum(budget_schedules.bs_time)" _
& " AS [Man Hours] FROM
(qryFabricCondition
LEFT JOIN analysis_codes" _
& " ON
qryFabricCondition.ad_analysis_code =
analysis_codes.ad_analysis_code)" _
& " LEFT JOIN budget_schedules ON
qryFabricCondition.fc_id = budget_schedules.fc_fabric_key" _
& " GROUP BY
analysis_codes.ad_description,
qryFabricCondition.ad_analysis_code,
qryFabricCondition.fc_program_year" _
& " HAVING
(((qryFabricCondition.fc_program_year)=1)) ORDER BY
qryFabricCondition.ad_analysis_code:"

Me.Label0.Caption = "Scenario 1"
'MsgBox "Case 1 Selected"

Case 2 '====================
Case 3 '==================== 'etc.

End Select

Me!Graph1.RowSource = strRecSource1
Me!Graph1.Requery


Exit Sub

Sub_ERR_Report_Open:
MsgBox "Error number " & Err.Number & ": " & Err.description

End Sub
============================================================================
Click to expand...
Click to expand...
Click to expand...
 
Back
Top