Formatting a Chart Object

[billmahon]

I am creating a report which contains a bar chart. There is to be a seperate
chart for each category, which I have accompished. Now, I want the
individual bars for each of the charts to change color based on their values.


As a general example, lets say I am plotting students test scores by date on
the chart. I want to create a seperate chart for each child (this I know how
to do by using the linking fields when I create the chart, then setting up a
section header for each child). Now I want any test score less than 60 to
show up as red on their individual graph.

I have successfully changed the color of a chart in a report where there is
no group levels, but when I use the same code, i get an error telling me that
the recordset cannot be found.

Can anyone help me?

 

[Rob Parker]

Hi Bill,

I assume that the "same code" you refer to is the sample I posted in
response to a previous question of yours. I've just now had time to look at
this, and I find a different error - I get RTE 1004 "Unable to get the
SeriesCollection property of the Chart class". This occurred when I have a
linked chart in the Detail section, and there will be several individual
chart within that section (due to all records being shown consecutively in
the detail section, in the example I used).

If I add an additional field to my table, and place a unique record into
each one, then group on that field (thereby forcing only a single
record/chart into the detail section for each group), I still get the same
error.

However, I can get this to work (and I don't need grouping in the report) by
changing the line:
Set rst = db.OpenRecordset(Me.Controls("ChartName").RowSource)
to
Dim strSQL As String
...

strSQL = Left(Me.Controls("ChartName").RowSource,
Len(Me.Controls("ChartName").RowSource) - 1) 'remove trailing ;
strSQL = strSQL & " WHERE LinkFieldName = '" & Me.LinkFieldName & "';"
Set rst = db.OpenRecordset(strSQL)
where LinkFieldName is the name of the field linking the records in the
detail section with the chart object.

HTH,


Rob

 

[billmahon]

Rob,

You are correct, it is the same code. This also ties in with my subsequent
question regarding option groups.

Basically I want to select a particular category from the option group to
filter ther records for a form and print the appropriate chart for that
group. I want the bars to change color. I am having two issues, the first
relates to the bars, the second relates to the option group. I think this
code may solve both issues.

I am having an issue with this line:
strSQL = strSQL & " WHERE LinkFieldName = '" & Me.LinkFieldName & "';"
it won't compile (it tells me I have a syntax error. I tried to copy it
exactly as you have it (with an apostraphe and quotes) but I can't get it to
work.

Thanks for your help.

 

[Rob Parker]

Hi Bill,

If that's the exact line in your code, it will only work if the field you
are linking between your report's recordsource and the chart's rowsource is
named LinkFieldName, and I think that's quite unlikely. You need to change
both occurrances of LinkFieldName to the actual name of the field you are
linking on. An incorrect field name will give a compile error.

Another possible issue is that I have assumed that the link field is a text
field (you mentioned student names, IIRC); if the link field is actually a
numeric field (eg. StudentID), then the delimiting apostrophes are not
required, and must not be present. Note: this won't give a compile error -
it will give a run-time error. So, if the link field is a numeric field
named StudentID, the line would be:
strSQL = strSQL & " WHERE StudentID= " & Me.StudentID & ";"

A third possibility is if the field is a text field, and it's contents
contain any entries with an apostrophe (again, this will give a run-time
error, rather than a compile error). If that's a possibility (and likely if
the field contains surnames), then you need to replace the single-quote
delimiters with a pair of double-quote delimiters, thus:
strSQL = strSQL & " WHERE LinkFieldName = """ & Me.LinkFieldName & """;"

I saw your other post regarding option groups, but didn't understand what
you were asking, and wanting to do. The group you are posting to is an
Access Reports group; I couldn't figure out what form containing an option
group was involved, or how you were trying to use an option group to affect
a report. And I disagree that the code to solve your colouring of bars in
linked charts will solve your option group problem.

If you're still having unsolvable problems, you can send me a (stripped-down
if necessary) copy of your database (in Access 2002 or earlier format) and
I'll have a look at it for you. You can get my email address by removing
the obvious antispam bits from my posting address, which is
(e-mail address removed)

Again, HTH,

Rob

 

[billmahon]

I have continued to try and make this work, but have been unsuccessful so
far. I have taken you up on your offer and sent a copy of the DB to you at
the contact address provided.

Thanks for all your efforts, I greatly appreciate them.