Link between report and chart

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

I'm not a newbie to this (my history in the Access newsgroups will confirm
this) but now I find myself stumped. And incredibly frustrated - this
shouldn't be so hard, or fail to work as it's doing for me.

I have a report, which contains a chart (an object with OLE Class =
"Microsoft Graph Chart"). The report has a Record Source of
"qryPhasedCommitSpendFY_CA" ; the chart has the same query as its Row
Source. When I attempt to link the chart to the report, via the Field
Linker dialog accessed from the LinkChild Fields or Link Master Fields on
the chart object property dialog, I receive a message "Can't build a link
between unbound forms". Why can't I link the chart to the report in this
manner? Or, more to the point, how can I get the chart to be linked to the
report?

It seems that the only way I can get a chart linked to the report is to
create it via the wizard; however, that screws up the Row Source I want, and
converts it to a crosstab query (under the covers). If I do that, and then
change the Row Source for the chart to what I want (and not what Microsoft
thinks I want), nothing shows in my chart; the links are useless.

If I leave the chart as an unbound object, and try to set the records which
it returns by modifying the Row Source to use parameters from the report
(ie. set criteria in the Row Source to include a criteria such as "WHERE CA
= [reports].[rptPhasings].[CA]"), I still get nothing in the chart - its Row
Source returns no records.

In my efforts to solve this, I did at one point have this last approach
working with a single criterion fiield; however, my criteria actually
involve two fields, and when I added the second one everything went to hell
in a hand-basket, and I haven't been able to get back to that point since.

Details:

qryPhasedCommitSpendFY_CA returns the following fields:
FY
CA
CAName
Dataset
MnthYr
Phased
Commit
Spend
ProRata

The report is opened via a DoCmd.OpenReport which has "FY = " &
Forms("frmPhasings").Controls("cboxSelectFY") in its Where parameter - the
value is correct.

The report has groupings for FY and CA (in that order). The FY field is
bound to a textbox (currently visible, but will be hidden) in the FY Group
Header, and the CA and CAName fields are bound to textbox controls in the CA
Group Header. The chart is also in the CA group header.

Finally, I have the following statement in the CAHeader_Format event:
Me.Controls("chtPhasings").ChartTitle.Text = "Phased Commit & Spend -
" & Me.CAName
which is also throwing an error (using either Me.CAName or Me.txtCAName).
Again, this worked at one point.

I've tried Me.chtPhasings .Refresh and Me.chtPhasings.Requery statements in
the CA Group Header format and print events, to no avail.

Help desperately needed - my head is getting very sore from banging it
against the wall ;-)

TIA,

Rob
 
I have a report, which contains a chart (an object with OLE Class =
"Microsoft Graph Chart"). The report has a Record Source of
"qryPhasedCommitSpendFY_CA" ; the chart has the same query as its Row
Source. When I attempt to link the chart to the report, via the Field
Linker dialog accessed from the LinkChild Fields or Link Master Fields
on the chart object property dialog, I receive a message "Can't build a
link between unbound forms". Why can't I link the chart to the report
in this manner? Or, more to the point, how can I get the chart to be
linked to the report?
Click to expand...

The little pop-up assistant for linking will often give this message.
Just manually type the entries you want into the MasterLink and ChilcLink
properties.

As an FYI don't be surprised if the chart does not properly refresh even
when you have the linking fields set up correctly. This hasn't worked
reliably since the chart engine in Office 95. The chart often (usually)
takes longer to render than the rest of the report and the report does
not wait for the chart to "catch up". You end up either with the chart
reflecting the previous linked data or with whatever data snapshot that
is stored in the chart designer. The latter is often the sample "North,
South, West" data used at the start of the chart design process.
 
Thanks for that, Rick.

Now I can set the link fields without getting the error. But I'm still
having problems, and my charts are not displaying anything:

As I said in my original post, I'm also trying to dynamically set the chart
title to include the CAName. For that, even simple debug.print statements
are giving errors. In the CAHeader_Format event,
Debug.Print Me.txtCAName
works as expected (the textbox is bound to the CAName field), but
Debug.Print Me.CAName
gives RTE2465 "can't find the field 'CAName' referred to in your
expression" - even though the intellisense shows that to be available when I
type the debug.print statement!

Attempting to set the chart title using a statement such as
Me.chtPhasings.ChartTitle.Text = "Phased Commit & Spend - " &
Me.txtCAName
in either the CAHeader-Format or CAHEader_Print event (I think it should be
in the Format event, rather than the Print event - the latter is probably
too late to change things) gives RTE1004 "Unable to set the Text property of
the ChartTitle class".

Lastly, I'm using a few lines of code which I found posted by Duane Hookom
in a thread from about 2 years ago titled "Data in report won't display
correctly", in the CAHEader_Print event:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
Even this (previously reported to solve a similar problem) is not having any
effect. I expected (hoped for!) it to solve the problem you mentioned in
your FYI para.

How can I get my charts to display correctly - or at all? As I also said in
my original post, at one point all these things were working as expected.
I'm completely at a loss as to why are these errors occurring - and some,
like the Debug.Print CAName one, seems absolutely insane to me!

Again, TIA,

Rob
 
I assume your Link field is CA. Could you just place a text box over/on the
chart control with the control source of:
="Phased Commit & Spend - " & Me.txtCAName

The code in a report can only reference values bound to controls (but I
expect you knew that).
 
Thanks Duane,

Assumption is correct - the link field is CA. I could use the workaround
for the CAName - shift my txtCAName textbox over the chart, and change its
control source to include the quoted string. But I find it distinctly
unsatisfying to use a kludge when there's a simple "correct" way - not, mind
you, that I'm averse to work-arounds when needed; I've posted some to help
others over the years (and perhaps you've seen a phrase I occasionally use
when I do so - "you can do anything if you're bloody-minded enough"). And
yes, I do know that code in a report can only reference bound fields.

Any idea as to why the debug.print statement which references a bound field
fails?

And most importantly, any ideas on how to get my report to display the
charts, rather than empty chart controls? The label workaround is pointless
if there's nothing to see ;-)

Rob
 
Can you share the SQL view of your report/row source? Here is some code that
I use in a form but it should be similar in the On Format event of the report
section containing the chart control:
Private Sub txtTitle_AfterUpdate()
On Error GoTo txtTitle_AfterUpdate_Err
Dim strErrMsg As String 'For Error Handling

Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value '"""" & Me.txtTitle &
""""

txtTitle_AfterUpdate_Exit:
On Error Resume Next
Exit Sub

txtTitle_AfterUpdate_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description
MsgBox strErrMsg, vbInformation, "txtTitle_AfterUpdate"
Resume txtTitle_AfterUpdate_Exit
End Select
End Sub


Some times stuff just gets corrupt for whatever reason. This may be the case.
 
The report and the chart are both based on a (fairly complex) query named
qryPhasedCommitSpendFY_CA. Its SQL is as follows:

SELECT qryCADatasetsFY.FY, qryCADatasetsFY.CA, tblCA.FullName AS CAName,
qryCADatasetsFY.Dataset, qryCADatasetsFY.MnthYr, qryPhasingsFYCA_Cum.Phased,
qryOrdersFYTotalsCA.SumOfCommit AS [Commit], qryOrdersFYTotalsCA.SumOfSpend
AS Spend, CCur(nz([ProRata],0)) AS [Pro Rata]
FROM (((qryCADatasetsFY LEFT JOIN qryPhasingsFYCA_Cum ON (qryCADatasetsFY.CA
= qryPhasingsFYCA_Cum.CA) AND (qryCADatasetsFY.Dataset =
qryPhasingsFYCA_Cum.Dataset)) LEFT JOIN qryOrdersFYTotalsCA ON
(qryCADatasetsFY.CA = qryOrdersFYTotalsCA.CA) AND (qryCADatasetsFY.Dataset =
qryOrdersFYTotalsCA.DataSet)) LEFT JOIN qryPhasingsFYCA_ProRata ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_ProRata.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_ProRata.Dataset)) INNER JOIN
tblCA ON qryCADatasetsFY.CA = tblCA.CAID
ORDER BY qryCADatasetsFY.FY, qryCADatasetsFY.CA, qryCADatasetsFY.Dataset;

This returns the records I expect. Indeed, if I display the data in the
report's detail section, it's exactly what should be plotted in the chart in
the CAHeader. So it's not as if there's some odd filtering going on that's
removing records, or the query is not returning any records. It's that they
won't show in my chart(s) :-(

I'm a little confused by a line in the code you posted - I don't see how it
can fail to throw an error. It's the only "functional" line, and it seems
to be missing at least one & character and one ' character.

I've done a compact/repair on my database (several, actually; and I've got
another almost identical report which is giving the same problem), so I
don't think it's a corruption issue.

Rob
 
There was only one functional line and nearly half of it (everything after
the "value") was commented out.

If you look at every CA field in the queries are they all numeric? Are the
all left or right aligned?

--
Duane Hookom
Microsoft Access MVP


Rob Parker said:
The report and the chart are both based on a (fairly complex) query named
qryPhasedCommitSpendFY_CA. Its SQL is as follows:

SELECT qryCADatasetsFY.FY, qryCADatasetsFY.CA, tblCA.FullName AS CAName,
qryCADatasetsFY.Dataset, qryCADatasetsFY.MnthYr, qryPhasingsFYCA_Cum.Phased,
qryOrdersFYTotalsCA.SumOfCommit AS [Commit], qryOrdersFYTotalsCA.SumOfSpend
AS Spend, CCur(nz([ProRata],0)) AS [Pro Rata]
FROM (((qryCADatasetsFY LEFT JOIN qryPhasingsFYCA_Cum ON (qryCADatasetsFY.CA
= qryPhasingsFYCA_Cum.CA) AND (qryCADatasetsFY.Dataset =
qryPhasingsFYCA_Cum.Dataset)) LEFT JOIN qryOrdersFYTotalsCA ON
(qryCADatasetsFY.CA = qryOrdersFYTotalsCA.CA) AND (qryCADatasetsFY.Dataset =
qryOrdersFYTotalsCA.DataSet)) LEFT JOIN qryPhasingsFYCA_ProRata ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_ProRata.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_ProRata.Dataset)) INNER JOIN
tblCA ON qryCADatasetsFY.CA = tblCA.CAID
ORDER BY qryCADatasetsFY.FY, qryCADatasetsFY.CA, qryCADatasetsFY.Dataset;

This returns the records I expect. Indeed, if I display the data in the
report's detail section, it's exactly what should be plotted in the chart in
the CAHeader. So it's not as if there's some odd filtering going on that's
removing records, or the query is not returning any records. It's that they
won't show in my chart(s) :-(

I'm a little confused by a line in the code you posted - I don't see how it
can fail to throw an error. It's the only "functional" line, and it seems
to be missing at least one & character and one ' character.

I've done a compact/repair on my database (several, actually; and I've got
another almost identical report which is giving the same problem), so I
don't think it's a corruption issue.

Rob

Duane said:
Can you share the SQL view of your report/row source? Here is some
code that I use in a form but it should be similar in the On Format
event of the report section containing the chart control:
Private Sub txtTitle_AfterUpdate()
On Error GoTo txtTitle_AfterUpdate_Err
Dim strErrMsg As String 'For Error Handling

Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value '"""" &
Me.txtTitle & """"

txtTitle_AfterUpdate_Exit:
On Error Resume Next
Exit Sub

txtTitle_AfterUpdate_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number)
& vbCrLf strErrMsg = strErrMsg & "Error Description: " &
Err.Description MsgBox strErrMsg, vbInformation,
"txtTitle_AfterUpdate" Resume txtTitle_AfterUpdate_Exit
End Select
End Sub


Some times stuff just gets corrupt for whatever reason. This may be
the case.
Click to expand...
Click to expand...
 
Duh!!! I've been staring at things for too long ;-)

CA is a long integer. It's a foreign key from CAID in the underlying tblCA
(several queries below the one I showed). And nothing has happened to it to
force it to another datatype (eg. string). It's also used in the SQL I
posted to join to tblCA to get CAName.

Rob

Duane said:
There was only one functional line and nearly half of it (everything
after the "value") was commented out.

If you look at every CA field in the queries are they all numeric?
Are the all left or right aligned?

The report and the chart are both based on a (fairly complex) query
named qryPhasedCommitSpendFY_CA. Its SQL is as follows:

SELECT qryCADatasetsFY.FY, qryCADatasetsFY.CA, tblCA.FullName AS
CAName, qryCADatasetsFY.Dataset, qryCADatasetsFY.MnthYr,
qryPhasingsFYCA_Cum.Phased, qryOrdersFYTotalsCA.SumOfCommit AS
[Commit], qryOrdersFYTotalsCA.SumOfSpend AS Spend,
CCur(nz([ProRata],0)) AS [Pro Rata]
FROM (((qryCADatasetsFY LEFT JOIN qryPhasingsFYCA_Cum ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_Cum.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_Cum.Dataset)) LEFT JOIN
qryOrdersFYTotalsCA ON (qryCADatasetsFY.CA = qryOrdersFYTotalsCA.CA)
AND (qryCADatasetsFY.Dataset = qryOrdersFYTotalsCA.DataSet)) LEFT
JOIN qryPhasingsFYCA_ProRata ON (qryCADatasetsFY.CA =
qryPhasingsFYCA_ProRata.CA) AND (qryCADatasetsFY.Dataset =
qryPhasingsFYCA_ProRata.Dataset)) INNER JOIN tblCA ON
qryCADatasetsFY.CA = tblCA.CAID
ORDER BY qryCADatasetsFY.FY, qryCADatasetsFY.CA,
qryCADatasetsFY.Dataset;

This returns the records I expect. Indeed, if I display the data in
the report's detail section, it's exactly what should be plotted in
the chart in the CAHeader. So it's not as if there's some odd
filtering going on that's removing records, or the query is not
returning any records. It's that they won't show in my chart(s) :-(

I'm a little confused by a line in the code you posted - I don't see
how it can fail to throw an error. It's the only "functional" line,
and it seems to be missing at least one & character and one '
character.

I've done a compact/repair on my database (several, actually; and
I've got another almost identical report which is giving the same
problem), so I don't think it's a corruption issue.

Rob

Duane said:
Can you share the SQL view of your report/row source? Here is some
code that I use in a form but it should be similar in the On Format
event of the report section containing the chart control:
Private Sub txtTitle_AfterUpdate()
On Error GoTo txtTitle_AfterUpdate_Err
Dim strErrMsg As String 'For Error Handling

Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value '"""" &
Me.txtTitle & """"

txtTitle_AfterUpdate_Exit:
On Error Resume Next
Exit Sub

txtTitle_AfterUpdate_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number)
& vbCrLf strErrMsg = strErrMsg & "Error Description: " &
Err.Description MsgBox strErrMsg, vbInformation,
"txtTitle_AfterUpdate" Resume txtTitle_AfterUpdate_Exit
End Select
End Sub


Some times stuff just gets corrupt for whatever reason. This may be
the case.

Thanks Duane,

Assumption is correct - the link field is CA. I could use the
workaround for the CAName - shift my txtCAName textbox over the
chart, and change its control source to include the quoted string.
But I find it distinctly unsatisfying to use a kludge when there's
a simple "correct" way - not, mind you, that I'm averse to
work-arounds when needed; I've posted some to help others over the
years (and perhaps you've seen a phrase I occasionally use when I
do so - "you can do anything if you're bloody-minded enough"). And
yes, I do know that code in a report can only reference bound
fields.

Any idea as to why the debug.print statement which references a
bound field fails?

And most importantly, any ideas on how to get my report to display
the charts, rather than empty chart controls? The label workaround
is pointless if there's nothing to see ;-)

Rob


Duane Hookom wrote:
I assume your Link field is CA. Could you just place a text box
over/on the chart control with the control source of:
="Phased Commit & Spend - " & Me.txtCAName

The code in a report can only reference values bound to controls
(but I expect you knew that).


Thanks for that, Rick.

Now I can set the link fields without getting the error. But I'm
still having problems, and my charts are not displaying anything:

As I said in my original post, I'm also trying to dynamically set
the chart title to include the CAName. For that, even simple
debug.print statements are giving errors. In the CAHeader_Format
event, Debug.Print Me.txtCAName
works as expected (the textbox is bound to the CAName field), but
Debug.Print Me.CAName
gives RTE2465 "can't find the field 'CAName' referred to in your
expression" - even though the intellisense shows that to be
available when I type the debug.print statement!

Attempting to set the chart title using a statement such as
Me.chtPhasings.ChartTitle.Text = "Phased Commit & Spend - " &
Me.txtCAName
in either the CAHeader-Format or CAHEader_Print event (I think it
should be in the Format event, rather than the Print event - the
latter is probably too late to change things) gives RTE1004
"Unable to set the Text property of the ChartTitle class".

Lastly, I'm using a few lines of code which I found posted by
Duane Hookom in a thread from about 2 years ago titled "Data in
report won't display correctly", in the CAHEader_Print event:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
Even this (previously reported to solve a similar problem) is not
having any effect. I expected (hoped for!) it to solve the
problem you mentioned in your FYI para.

How can I get my charts to display correctly - or at all? As I
also said in my original post, at one point all these things were
working as expected. I'm completely at a loss as to why are these
errors occurring - and some, like the Debug.Print CAName one,
seems absolutely insane to me!

Again, TIA,

Rob


Rick Brandt wrote:
On Sun, 21 Dec 2008 00:28:44 +1100, Rob Parker wrote:
I have a report, which contains a chart (an object with OLE
Class = "Microsoft Graph Chart"). The report has a Record
Source of "qryPhasedCommitSpendFY_CA" ; the chart has the same
query as its Row Source. When I attempt to link the chart to
the report, via the Field Linker dialog accessed from the
LinkChild Fields or Link Master Fields on the chart object
property dialog, I receive a message "Can't build a link
between unbound forms". Why can't I link the chart to the
report in this manner? Or, more to the point, how can I get
the chart to be linked to the report?

The little pop-up assistant for linking will often give this
message. Just manually type the entries you want into the
MasterLink and ChilcLink properties.

As an FYI don't be surprised if the chart does not properly
refresh even when you have the linking fields set up correctly.
This hasn't worked reliably since the chart engine in Office 95.
The chart often (usually) takes longer to render than the rest
of the report and the report does not wait for the chart to
"catch up". You end up either with the chart reflecting the
previous linked data or with whatever data snapshot that is
stored in the chart designer. The latter is often the sample
"North, South, West" data used at the start of the chart design
process.
Click to expand...
Click to expand...
Click to expand...
 
So, what that the primary issue? Did any of your problems get resolved?

--
Duane Hookom
Microsoft Access MVP


Rob Parker said:
Duh!!! I've been staring at things for too long ;-)

CA is a long integer. It's a foreign key from CAID in the underlying tblCA
(several queries below the one I showed). And nothing has happened to it to
force it to another datatype (eg. string). It's also used in the SQL I
posted to join to tblCA to get CAName.

Rob

Duane said:
There was only one functional line and nearly half of it (everything
after the "value") was commented out.

If you look at every CA field in the queries are they all numeric?
Are the all left or right aligned?

The report and the chart are both based on a (fairly complex) query
named qryPhasedCommitSpendFY_CA. Its SQL is as follows:

SELECT qryCADatasetsFY.FY, qryCADatasetsFY.CA, tblCA.FullName AS
CAName, qryCADatasetsFY.Dataset, qryCADatasetsFY.MnthYr,
qryPhasingsFYCA_Cum.Phased, qryOrdersFYTotalsCA.SumOfCommit AS
[Commit], qryOrdersFYTotalsCA.SumOfSpend AS Spend,
CCur(nz([ProRata],0)) AS [Pro Rata]
FROM (((qryCADatasetsFY LEFT JOIN qryPhasingsFYCA_Cum ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_Cum.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_Cum.Dataset)) LEFT JOIN
qryOrdersFYTotalsCA ON (qryCADatasetsFY.CA = qryOrdersFYTotalsCA.CA)
AND (qryCADatasetsFY.Dataset = qryOrdersFYTotalsCA.DataSet)) LEFT
JOIN qryPhasingsFYCA_ProRata ON (qryCADatasetsFY.CA =
qryPhasingsFYCA_ProRata.CA) AND (qryCADatasetsFY.Dataset =
qryPhasingsFYCA_ProRata.Dataset)) INNER JOIN tblCA ON
qryCADatasetsFY.CA = tblCA.CAID
ORDER BY qryCADatasetsFY.FY, qryCADatasetsFY.CA,
qryCADatasetsFY.Dataset;

This returns the records I expect. Indeed, if I display the data in
the report's detail section, it's exactly what should be plotted in
the chart in the CAHeader. So it's not as if there's some odd
filtering going on that's removing records, or the query is not
returning any records. It's that they won't show in my chart(s) :-(

I'm a little confused by a line in the code you posted - I don't see
how it can fail to throw an error. It's the only "functional" line,
and it seems to be missing at least one & character and one '
character.

I've done a compact/repair on my database (several, actually; and
I've got another almost identical report which is giving the same
problem), so I don't think it's a corruption issue.

Rob

Duane Hookom wrote:
Can you share the SQL view of your report/row source? Here is some
code that I use in a form but it should be similar in the On Format
event of the report section containing the chart control:
Private Sub txtTitle_AfterUpdate()
On Error GoTo txtTitle_AfterUpdate_Err
Dim strErrMsg As String 'For Error Handling

Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value '"""" &
Me.txtTitle & """"

txtTitle_AfterUpdate_Exit:
On Error Resume Next
Exit Sub

txtTitle_AfterUpdate_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number)
& vbCrLf strErrMsg = strErrMsg & "Error Description: " &
Err.Description MsgBox strErrMsg, vbInformation,
"txtTitle_AfterUpdate" Resume txtTitle_AfterUpdate_Exit
End Select
End Sub


Some times stuff just gets corrupt for whatever reason. This may be
the case.

Thanks Duane,

Assumption is correct - the link field is CA. I could use the
workaround for the CAName - shift my txtCAName textbox over the
chart, and change its control source to include the quoted string.
But I find it distinctly unsatisfying to use a kludge when there's
a simple "correct" way - not, mind you, that I'm averse to
work-arounds when needed; I've posted some to help others over the
years (and perhaps you've seen a phrase I occasionally use when I
do so - "you can do anything if you're bloody-minded enough"). And
yes, I do know that code in a report can only reference bound
fields.

Any idea as to why the debug.print statement which references a
bound field fails?

And most importantly, any ideas on how to get my report to display
the charts, rather than empty chart controls? The label workaround
is pointless if there's nothing to see ;-)

Rob


Duane Hookom wrote:
I assume your Link field is CA. Could you just place a text box
over/on the chart control with the control source of:
="Phased Commit & Spend - " & Me.txtCAName

The code in a report can only reference values bound to controls
(but I expect you knew that).


Thanks for that, Rick.

Now I can set the link fields without getting the error. But I'm
still having problems, and my charts are not displaying anything:

As I said in my original post, I'm also trying to dynamically set
the chart title to include the CAName. For that, even simple
debug.print statements are giving errors. In the CAHeader_Format
event, Debug.Print Me.txtCAName
works as expected (the textbox is bound to the CAName field), but
Debug.Print Me.CAName
gives RTE2465 "can't find the field 'CAName' referred to in your
expression" - even though the intellisense shows that to be
available when I type the debug.print statement!

Attempting to set the chart title using a statement such as
Me.chtPhasings.ChartTitle.Text = "Phased Commit & Spend - " &
Me.txtCAName
in either the CAHeader-Format or CAHEader_Print event (I think it
should be in the Format event, rather than the Print event - the
latter is probably too late to change things) gives RTE1004
"Unable to set the Text property of the ChartTitle class".

Lastly, I'm using a few lines of code which I found posted by
Duane Hookom in a thread from about 2 years ago titled "Data in
report won't display correctly", in the CAHEader_Print event:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
Even this (previously reported to solve a similar problem) is not
having any effect. I expected (hoped for!) it to solve the
problem you mentioned in your FYI para.

How can I get my charts to display correctly - or at all? As I
also said in my original post, at one point all these things were
working as expected. I'm completely at a loss as to why are these
errors occurring - and some, like the Debug.Print CAName one,
seems absolutely insane to me!

Again, TIA,

Rob


Rick Brandt wrote:
On Sun, 21 Dec 2008 00:28:44 +1100, Rob Parker wrote:
I have a report, which contains a chart (an object with OLE
Class = "Microsoft Graph Chart"). The report has a Record
Source of "qryPhasedCommitSpendFY_CA" ; the chart has the same
query as its Row Source. When I attempt to link the chart to
the report, via the Field Linker dialog accessed from the
LinkChild Fields or Link Master Fields on the chart object
property dialog, I receive a message "Can't build a link
between unbound forms". Why can't I link the chart to the
report in this manner? Or, more to the point, how can I get
the chart to be linked to the report?

The little pop-up assistant for linking will often give this
message. Just manually type the entries you want into the
MasterLink and ChilcLink properties.

As an FYI don't be surprised if the chart does not properly
refresh even when you have the linking fields set up correctly.
This hasn't worked reliably since the chart engine in Office 95.
The chart often (usually) takes longer to render than the rest
of the report and the report does not wait for the chart to
"catch up". You end up either with the chart reflecting the
previous linked data or with whatever data snapshot that is
stored in the chart designer. The latter is often the sample
"North, South, West" data used at the start of the chart design
process.
Click to expand...
Click to expand...
Click to expand...
 
Sorry Duane, that wasn't the primary issue.

My major problem is that nothing displays in my charts - even though the
data is correct, and with code you posted a couple of years ago to allow
time for the chart object to refresh correctly (see my second post in this
thread, after Rick Brandt's initial response). I've just tried dumping the
output from my report/chart recordsource query to a temporary table
(manually, before opening the report), and basing the report/chart on that,
but again no joy - my charts stay blank. I'm willing to try any possible
suggestions to get my chart data to display.

My second problem is that I can't set the ChartTitle.Text property - but I
could use the overlaid textbox work-around you suggested for that.

Rob

Duane said:
So, what that the primary issue? Did any of your problems get
resolved?

Duh!!! I've been staring at things for too long ;-)

CA is a long integer. It's a foreign key from CAID in the
underlying tblCA (several queries below the one I showed). And
nothing has happened to it to force it to another datatype (eg.
string). It's also used in the SQL I posted to join to tblCA to get
CAName.

Rob

Duane said:
There was only one functional line and nearly half of it (everything
after the "value") was commented out.

If you look at every CA field in the queries are they all numeric?
Are the all left or right aligned?


The report and the chart are both based on a (fairly complex) query
named qryPhasedCommitSpendFY_CA. Its SQL is as follows:

SELECT qryCADatasetsFY.FY, qryCADatasetsFY.CA, tblCA.FullName AS
CAName, qryCADatasetsFY.Dataset, qryCADatasetsFY.MnthYr,
qryPhasingsFYCA_Cum.Phased, qryOrdersFYTotalsCA.SumOfCommit AS
[Commit], qryOrdersFYTotalsCA.SumOfSpend AS Spend,
CCur(nz([ProRata],0)) AS [Pro Rata]
FROM (((qryCADatasetsFY LEFT JOIN qryPhasingsFYCA_Cum ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_Cum.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_Cum.Dataset)) LEFT JOIN
qryOrdersFYTotalsCA ON (qryCADatasetsFY.CA =
qryOrdersFYTotalsCA.CA) AND (qryCADatasetsFY.Dataset =
qryOrdersFYTotalsCA.DataSet)) LEFT JOIN qryPhasingsFYCA_ProRata ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_ProRata.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_ProRata.Dataset)) INNER
JOIN tblCA ON qryCADatasetsFY.CA = tblCA.CAID
ORDER BY qryCADatasetsFY.FY, qryCADatasetsFY.CA,
qryCADatasetsFY.Dataset;

This returns the records I expect. Indeed, if I display the data
in the report's detail section, it's exactly what should be
plotted in the chart in the CAHeader. So it's not as if there's
some odd filtering going on that's removing records, or the query
is not returning any records. It's that they won't show in my
chart(s) :-(

I'm a little confused by a line in the code you posted - I don't
see how it can fail to throw an error. It's the only "functional"
line, and it seems to be missing at least one & character and one '
character.

I've done a compact/repair on my database (several, actually; and
I've got another almost identical report which is giving the same
problem), so I don't think it's a corruption issue.

Rob

Duane Hookom wrote:
Can you share the SQL view of your report/row source? Here is some
code that I use in a form but it should be similar in the On
Format event of the report section containing the chart control:
Private Sub txtTitle_AfterUpdate()
On Error GoTo txtTitle_AfterUpdate_Err
Dim strErrMsg As String 'For Error Handling

Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value '"""" &
Me.txtTitle & """"

txtTitle_AfterUpdate_Exit:
On Error Resume Next
Exit Sub

txtTitle_AfterUpdate_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " &
Format$(Err.Number) & vbCrLf strErrMsg = strErrMsg &
"Error Description: " & Err.Description MsgBox
strErrMsg, vbInformation, "txtTitle_AfterUpdate"
Resume txtTitle_AfterUpdate_Exit End Select
End Sub


Some times stuff just gets corrupt for whatever reason. This may
be the case.

Thanks Duane,

Assumption is correct - the link field is CA. I could use the
workaround for the CAName - shift my txtCAName textbox over the
chart, and change its control source to include the quoted
string. But I find it distinctly unsatisfying to use a kludge
when there's a simple "correct" way - not, mind you, that I'm
averse to work-arounds when needed; I've posted some to help
others over the years (and perhaps you've seen a phrase I
occasionally use when I do so - "you can do anything if you're
bloody-minded enough"). And yes, I do know that code in a
report can only reference bound fields.

Any idea as to why the debug.print statement which references a
bound field fails?

And most importantly, any ideas on how to get my report to
display the charts, rather than empty chart controls? The label
workaround is pointless if there's nothing to see ;-)

Rob


Duane Hookom wrote:
I assume your Link field is CA. Could you just place a text box
over/on the chart control with the control source of:
="Phased Commit & Spend - " & Me.txtCAName

The code in a report can only reference values bound to controls
(but I expect you knew that).


Thanks for that, Rick.

Now I can set the link fields without getting the error. But
I'm still having problems, and my charts are not displaying
anything:

As I said in my original post, I'm also trying to dynamically
set the chart title to include the CAName. For that, even
simple debug.print statements are giving errors. In the
CAHeader_Format event, Debug.Print Me.txtCAName
works as expected (the textbox is bound to the CAName field),
but Debug.Print Me.CAName
gives RTE2465 "can't find the field 'CAName' referred to in
your expression" - even though the intellisense shows that to
be available when I type the debug.print statement!

Attempting to set the chart title using a statement such as
Me.chtPhasings.ChartTitle.Text = "Phased Commit & Spend -
" & Me.txtCAName
in either the CAHeader-Format or CAHEader_Print event (I think
it should be in the Format event, rather than the Print event
- the latter is probably too late to change things) gives
RTE1004 "Unable to set the Text property of the ChartTitle
class".

Lastly, I'm using a few lines of code which I found posted by
Duane Hookom in a thread from about 2 years ago titled "Data in
report won't display correctly", in the CAHEader_Print event:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
Even this (previously reported to solve a similar problem) is
not having any effect. I expected (hoped for!) it to solve the
problem you mentioned in your FYI para.

How can I get my charts to display correctly - or at all? As I
also said in my original post, at one point all these things
were working as expected. I'm completely at a loss as to why
are these errors occurring - and some, like the Debug.Print
CAName one, seems absolutely insane to me!

Again, TIA,

Rob


Rick Brandt wrote:
On Sun, 21 Dec 2008 00:28:44 +1100, Rob Parker wrote:
I have a report, which contains a chart (an object with OLE
Class = "Microsoft Graph Chart"). The report has a Record
Source of "qryPhasedCommitSpendFY_CA" ; the chart has the
same query as its Row Source. When I attempt to link the
chart to the report, via the Field Linker dialog accessed
from the LinkChild Fields or Link Master Fields on the chart
object property dialog, I receive a message "Can't build a
link between unbound forms". Why can't I link the chart to
the report in this manner? Or, more to the point, how can I
get the chart to be linked to the report?

The little pop-up assistant for linking will often give this
message. Just manually type the entries you want into the
MasterLink and ChilcLink properties.

As an FYI don't be surprised if the chart does not properly
refresh even when you have the linking fields set up
correctly. This hasn't worked reliably since the chart engine
in Office 95. The chart often (usually) takes longer to
render than the rest of the report and the report does not
wait for the chart to "catch up". You end up either with the
chart reflecting the previous linked data or with whatever
data snapshot that is stored in the chart designer. The
latter is often the sample "North, South, West" data used at
the start of the chart design process.
Click to expand...
Click to expand...
Click to expand...
 
Rob,

I am willing to take a look at your file. You would need to create a new mdb
with just the required objects, compact, zip, and send it to me at
duanehookom AT gmail DOT com.
--
Duane Hookom
Microsoft Access MVP


Rob Parker said:
Sorry Duane, that wasn't the primary issue.

My major problem is that nothing displays in my charts - even though the
data is correct, and with code you posted a couple of years ago to allow
time for the chart object to refresh correctly (see my second post in this
thread, after Rick Brandt's initial response). I've just tried dumping the
output from my report/chart recordsource query to a temporary table
(manually, before opening the report), and basing the report/chart on that,
but again no joy - my charts stay blank. I'm willing to try any possible
suggestions to get my chart data to display.

My second problem is that I can't set the ChartTitle.Text property - but I
could use the overlaid textbox work-around you suggested for that.

Rob

Duane said:
So, what that the primary issue? Did any of your problems get
resolved?

Duh!!! I've been staring at things for too long ;-)

CA is a long integer. It's a foreign key from CAID in the
underlying tblCA (several queries below the one I showed). And
nothing has happened to it to force it to another datatype (eg.
string). It's also used in the SQL I posted to join to tblCA to get
CAName.

Rob

Duane Hookom wrote:
There was only one functional line and nearly half of it (everything
after the "value") was commented out.

If you look at every CA field in the queries are they all numeric?
Are the all left or right aligned?


The report and the chart are both based on a (fairly complex) query
named qryPhasedCommitSpendFY_CA. Its SQL is as follows:

SELECT qryCADatasetsFY.FY, qryCADatasetsFY.CA, tblCA.FullName AS
CAName, qryCADatasetsFY.Dataset, qryCADatasetsFY.MnthYr,
qryPhasingsFYCA_Cum.Phased, qryOrdersFYTotalsCA.SumOfCommit AS
[Commit], qryOrdersFYTotalsCA.SumOfSpend AS Spend,
CCur(nz([ProRata],0)) AS [Pro Rata]
FROM (((qryCADatasetsFY LEFT JOIN qryPhasingsFYCA_Cum ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_Cum.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_Cum.Dataset)) LEFT JOIN
qryOrdersFYTotalsCA ON (qryCADatasetsFY.CA =
qryOrdersFYTotalsCA.CA) AND (qryCADatasetsFY.Dataset =
qryOrdersFYTotalsCA.DataSet)) LEFT JOIN qryPhasingsFYCA_ProRata ON
(qryCADatasetsFY.CA = qryPhasingsFYCA_ProRata.CA) AND
(qryCADatasetsFY.Dataset = qryPhasingsFYCA_ProRata.Dataset)) INNER
JOIN tblCA ON qryCADatasetsFY.CA = tblCA.CAID
ORDER BY qryCADatasetsFY.FY, qryCADatasetsFY.CA,
qryCADatasetsFY.Dataset;

This returns the records I expect. Indeed, if I display the data
in the report's detail section, it's exactly what should be
plotted in the chart in the CAHeader. So it's not as if there's
some odd filtering going on that's removing records, or the query
is not returning any records. It's that they won't show in my
chart(s) :-(

I'm a little confused by a line in the code you posted - I don't
see how it can fail to throw an error. It's the only "functional"
line, and it seems to be missing at least one & character and one '
character.

I've done a compact/repair on my database (several, actually; and
I've got another almost identical report which is giving the same
problem), so I don't think it's a corruption issue.

Rob

Duane Hookom wrote:
Can you share the SQL view of your report/row source? Here is some
code that I use in a form but it should be similar in the On
Format event of the report section containing the chart control:
Private Sub txtTitle_AfterUpdate()
On Error GoTo txtTitle_AfterUpdate_Err
Dim strErrMsg As String 'For Error Handling

Me.grpGraph.ChartTitle.Text = Me.txtTitle.Value '"""" &
Me.txtTitle & """"

txtTitle_AfterUpdate_Exit:
On Error Resume Next
Exit Sub

txtTitle_AfterUpdate_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " &
Format$(Err.Number) & vbCrLf strErrMsg = strErrMsg &
"Error Description: " & Err.Description MsgBox
strErrMsg, vbInformation, "txtTitle_AfterUpdate"
Resume txtTitle_AfterUpdate_Exit End Select
End Sub


Some times stuff just gets corrupt for whatever reason. This may
be the case.

Thanks Duane,

Assumption is correct - the link field is CA. I could use the
workaround for the CAName - shift my txtCAName textbox over the
chart, and change its control source to include the quoted
string. But I find it distinctly unsatisfying to use a kludge
when there's a simple "correct" way - not, mind you, that I'm
averse to work-arounds when needed; I've posted some to help
others over the years (and perhaps you've seen a phrase I
occasionally use when I do so - "you can do anything if you're
bloody-minded enough"). And yes, I do know that code in a
report can only reference bound fields.

Any idea as to why the debug.print statement which references a
bound field fails?

And most importantly, any ideas on how to get my report to
display the charts, rather than empty chart controls? The label
workaround is pointless if there's nothing to see ;-)

Rob


Duane Hookom wrote:
I assume your Link field is CA. Could you just place a text box
over/on the chart control with the control source of:
="Phased Commit & Spend - " & Me.txtCAName

The code in a report can only reference values bound to controls
(but I expect you knew that).


Thanks for that, Rick.

Now I can set the link fields without getting the error. But
I'm still having problems, and my charts are not displaying
anything:

As I said in my original post, I'm also trying to dynamically
set the chart title to include the CAName. For that, even
simple debug.print statements are giving errors. In the
CAHeader_Format event, Debug.Print Me.txtCAName
works as expected (the textbox is bound to the CAName field),
but Debug.Print Me.CAName
gives RTE2465 "can't find the field 'CAName' referred to in
your expression" - even though the intellisense shows that to
be available when I type the debug.print statement!

Attempting to set the chart title using a statement such as
Me.chtPhasings.ChartTitle.Text = "Phased Commit & Spend -
" & Me.txtCAName
in either the CAHeader-Format or CAHEader_Print event (I think
it should be in the Format event, rather than the Print event
- the latter is probably too late to change things) gives
RTE1004 "Unable to set the Text property of the ChartTitle
class".

Lastly, I'm using a few lines of code which I found posted by
Duane Hookom in a thread from about 2 years ago titled "Data in
report won't display correctly", in the CAHEader_Print event:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
Even this (previously reported to solve a similar problem) is
not having any effect. I expected (hoped for!) it to solve the
problem you mentioned in your FYI para.

How can I get my charts to display correctly - or at all? As I
also said in my original post, at one point all these things
were working as expected. I'm completely at a loss as to why
are these errors occurring - and some, like the Debug.Print
CAName one, seems absolutely insane to me!

Again, TIA,

Rob


Rick Brandt wrote:
On Sun, 21 Dec 2008 00:28:44 +1100, Rob Parker wrote:
I have a report, which contains a chart (an object with OLE
Class = "Microsoft Graph Chart"). The report has a Record
Source of "qryPhasedCommitSpendFY_CA" ; the chart has the
same query as its Row Source. When I attempt to link the
chart to the report, via the Field Linker dialog accessed
from the LinkChild Fields or Link Master Fields on the chart
object property dialog, I receive a message "Can't build a
link between unbound forms". Why can't I link the chart to
the report in this manner? Or, more to the point, how can I
get the chart to be linked to the report?

The little pop-up assistant for linking will often give this
message. Just manually type the entries you want into the
MasterLink and ChilcLink properties.

As an FYI don't be surprised if the chart does not properly
refresh even when you have the linking fields set up
correctly. This hasn't worked reliably since the chart engine
in Office 95. The chart often (usually) takes longer to
render than the rest of the report and the report does not
wait for the chart to "catch up". You end up either with the
chart reflecting the previous linked data or with whatever
data snapshot that is stored in the chart designer. The
latter is often the sample "North, South, West" data used at
the start of the chart design process.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane,

Much appreciated. I'll trim it down and send it a.s.a.p. But not
immediately - a bit busy with pre-Christmas until this evening.

Rob

Duane said:
Rob,

I am willing to take a look at your file. You would need to create a
new mdb with just the required objects, compact, zip, and send it to
me at duanehookom AT gmail DOT com.
Click to expand...
<snip>
 
I think most of us who celebrate this holiday season are busy, particularly
guys who are still thinking about doing their shopping/buying. Tonight has
been wrapping home-made carmels and scrambling to find addresses from
returned Christmas cards.
 
Final follow-up: Duane saved my bacon, and the basic problem was simple -
and related to the issue that Dubug.Print statements would fail when I
attempted to print a field from the form's RecordSource (even though the
field was bound to a control on the form). To solve, all that was needed
was to change the Link Master field between the report and chart from CA
(the underlying field name) to txtCA (the control bound to that field).

Thanks Duane,

Rob
 
Final follow-up:  Duane saved my bacon, and the basic problem was simple -
and related to the issue that Dubug.Print statements would fail when I
attempted to print a field from the form's RecordSource (even though the
field was bound to a control on the form).  To solve, all that was needed
was to change the Link Master field between the report and chart from CA
(the underlying field name) to txtCA (the control bound to that field).

Thanks Duane,

Rob





- Show quoted text -
Click to expand...

This helped me tremendously! Thank you for the posting.

I was attempting to create a benefits statement for my company and
could not get a chart/graph to work in Word 2007 from a table and
thought about using Access. When I started using Access I had all
kinds of problems with getting the graph to format. My workaround the
microsoft bugs:

1. Created the graph in a Form first.
2. Copied the grap after getting it to display to the report.
3. Added the link field (employee_database_id) as a text box in the
footer section of the report adjacent to the graph. Named the text
box txtEmployee_Database_ID and set the visible property to "N".
4. Manually entered the Link Master Fields to =
txtEmployee_Database_ID (the control added in the previous step)
5. Manually entered the Link Child Fields to = employee_database_id.
This is the field returned by the query string in the Row Source
property of the graph control.

This allowed me to get my graph to display correctly for each employee.
 
Back
Top