Hi again Linda,
You've still got "ChartName" in the Debug.Print statement, so that won't be
giving you the SQL for your chart's RowSource, to confirm the fieldname
which you should be using. And, once you've done that and got your code
working correctly, you can either remove or comment out the Debug.Print
line.
You've also got four lines of code duplicated. And (if you've cut/pasted
the code directly from the VBA editor) your Set pnt ... statement should not
be continued from the previous line.
Your Case statements are now testing for values of 4 and 6, and the code
should change the colour of those points; points with any other value will
be unaffected, since they will not meet the criteria set in the Case
statements.
Try this (be careful of line wrap in your newsreader - and ensure that the
field name given by the Debug.Print statement really is "Social"; if it's
not, change it wherever it occurs):
Dim db As Database
Dim rst As DAO.Recordset
Dim pnt As Object
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(Me.Controls("RAS1").RowSource)
Debug.Print Me.Controls("RAS1").RowSource
With rst
.MoveFirst
For i = 1 To Me.Controls("RAS1").SeriesCollection("Social").Points().Count
Set pnt = Me.Controls("RAS1").SeriesCollection("Social").Points(i)
Select Case !Social
Case Is <5
pnt.Interior.Color = vbRed 'set colour of bar
pnt.Border.Color = vbRed 'set colour of bar's border
Me.Controls("RAS1").SeriesCollection("Social").DataLabels(i).Font.Color
= vbRed
Case 5 to 6
pnt.Interior.Color = vbYellow
pnt.Border.Color = vbYellow
Me.Controls("RAS1").SeriesCollection("Social").DataLabels(i).Font.Color
= vbYellow
Case Is> 6
pnt.Interior.Color = vbGreen
pnt.Border.Color = vbGreen
Me.Controls("RAS1").SeriesCollection("Social").DataLabels(i).Font.Color
= vbGreen
Case Else
End Select
.MoveNext
Next i
End With
Set rst = Nothing
Set db = Nothing
Again, HTH,
Rob
PS. You haven't mentioned any error messages which you may have got from
your code. Is it correctly set up to run from the Format event of the
section of the report in which the chart control is located (ie. does the On
format field in the Property dialog for the section contain [Event
Procedure], and when you double-click the ... to the right of that, does
your code appear between Private Sub ... and End Sub lines)?
Rob
Nothing is happening when using the versionof your code, it does not
seem to be working.
About the Case statement how do you mean about acuracy the number wil
be whole numbers no decimals will be used.
Here is my revised code is it still wrong
Dim db As Database
Dim rst As DAO.Recordset
Dim pnt As Object
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(Me.Controls("RAS1").RowSource)
Set rst = db.OpenRecordset(Me.Controls("RAS1").RowSource)
Debug.Print Me.Controls("ChartName").RowSource 'this is the new line
With rst
With rst
.MoveFirst
For i = 1 To
Me.Controls("RAS1").SeriesCollection("Social").Points().Count Set pnt
= Me.Controls("RAS1").SeriesCollection("Social").Points(i)
Select Case !Social
Case Is = 4
pnt.Interior.Color = vbRed 'set colour of bar
pnt.Border.Color = vbRed 'set colour of bar's border
Me.Controls("RAS1").SeriesCollection("Social").DataLabels(i).Font.Color
= vbRed
Case Is = 6
pnt.Interior.Color = vbYellow
pnt.Border.Color = vbYellow
Me.Controls("RAS1").SeriesCollection("Social").DataLabels(i).Font.Color
= vbYellow
Case Is = 6
pnt.Interior.Color = vbGreen
pnt.Border.Color = vbGreen
Me.Controls("RAS1").SeriesCollection("Social").DataLabels(i).Font.Color
= vbGreen
Case Else
End Select
.MoveNext
Next i
End With
End With
Set rst = Nothing
Set rst = Nothing
Set db = Nothing
Rob Parker said:
Hi Linda,
You don't say what's happening now, using your version of my code.
Does it work, or not? I would expect that it doesn't, since you've
omitted some very important delimiter characters (double-quotes) in
your version, which are necessary to pass the names of objects
(controls, fields) when the code is running.
You need to place double-quote characters around "ChartName" and
"Social" whenever those names appear in brackets. In addition, you
need to replace Chartname with the name of the chart object in your
form/report. And, as I said in my last post, check the SQL
statement which the Debug.Print line will generate in the immediate
window of the VBA editor to ensure that Social is the name of the
field for which you want to change the colour of the bars - it may
be that the field is actually named SumOfSocial (or something else)
if you've used the chart wizard to generate your chart.
Also I note that your criteria (now used in the Case statements) are
not sufficiently accurate to cover the possible range of values.
For instance, if Social = 4 (if it's an integer datatype, or from 4
to 4.9999999999999 if it's not an integer), none of the Case
statements will be executed.
Again, HTH,
Rob
PS. I notice that your code has no indenting. You'll find it much
easier to follow through if you use indenting as shown in my
original code. Sure, the spaces at the start of lines seem a bit of
a waste, but (almost) every programmer will consider them to be
invaluable in following the program flow. It really is worth it ;-)
LindaBee wrote:
Rob
This is how I have changed your code
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rst As DAO.Recordset
Dim pnt As Object
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(Me.Controls(ChartName).RowSource)
Debug.Print Me.Controls(ChartName).RowSource 'this is the new line
With rst
.MoveFirst
For i = 1 To
Me.Controls(ChartName).SeriesCollection(Social).Points().Count Set
pnt = Me.Controls(ChartName).SeriesCollection(Social).Points(i)
Select Case !Social
Case Is < 4
pnt.Interior.Color = vbRed 'set colour of bar
pnt.Border.Color = vbRed 'set colour of bar's border
Me.Controls(ChartName).SeriesCollection(Social).DataLabels(i).Font.Color
= vbRed
Case 5 To 6
pnt.Interior.Color = vbYellow
pnt.Border.Color = vbYellow
Me.Controls(ChartName).SeriesCollection(Social).DataLabels(i).Font.Color
= vbYellow
Case Is > 6
pnt.Interior.Color = vbGreen
pnt.Border.Color = vbGreen
Me.Controls(ChartName).SeriesCollection(Social).DataLabels(i).Font.Color
= vbGreen
Case Else
End Select
.MoveNext
Next i
End With
Set rst = Nothing
Set db = Nothing
End Sub
Linda
:
Hi Linda,
Is that query, which will return 11 fields, the RowSource of your
chart object? Or is it the source of your report?
Perhaps this will help isolate the problem:
In the code I posted, add an additional line near the start,
between the Set rst ... and With rst ...lines, as follows:
Set rst = db.OpenRecordset(Me.Controls("ChartName").RowSource)
Debug.Print Me.Controls("ChartName").RowSource 'this is the new
line With rst
Open your report to run the code, then check the field names in the
SQL statement which will now be shown in the immediate window.
Substitute the name shown there for the field you want in the code
I posted.
HTH,
Rob
PS. It's just on midnight here, and I'm off to bed. I'll check
this thread again tomorrow morning.
LindaBee wrote:
Rob
This is my SQL
SELECT Table1.PersonRef, Table1.Health, Table1.Money,
Table1.Safety, Table1.Social, Table1.Choice, Table1.[Living
skills], Table1.Independence, Table1.mobility, Table1.culture,
Table1.[mental health]
FROM Table1;
lINDA
:
Hi Linda,
I'm not exactly sure what you've got - seems likely that your
source for the chart is a Totals query, in which case it may well
have a field name "SumOfSocial". Have you tried using that in
the code I gave?
If that doesn't work, and you need more help, please post the SQL
of the chart's RowSource.
Rob
LindaBee wrote:
Sorry my field name is Social but coming up as SumOfSocial
Linda
:
I have a query about colour coding the bars on a graph. I have
values in a table I would like to convert into a chart.
The values range from 1 to 10. What I want displayed on a chart
is if the number in a column is between 1 -4 then the bar
shoould be Red and if the numbers are 5 to 6 then the bar
should be amber and if 8 to 10 then the bar should be green.
Can this be done if so how
Thanks
Linda
