Formatting a Chart Object

B

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

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
 
B

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

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
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top