H
hughess7
Hi, I figured out this was just moving through the records and looking at one
column, I removed the line
.MoveNext
and now the report runs without any errors but it just produces all the bars
in red! So I need to figure out how to colour code each bar individually by
its value.
If anyone can help with this I would very much appreciate it thanks!!
Sue
column, I removed the line
.MoveNext
and now the report runs without any errors but it just produces all the bars
in red! So I need to figure out how to colour code each bar individually by
its value.
If anyone can help with this I would very much appreciate it thanks!!
Sue
hughess7 said:Hi, thanks for this great thread!! I am trying to achieve the same results
with my chart - i.e. colour the bar red if the value > 100 but I am having
trouble tweaking the code to match my scenario. Could you help please?
Code I have is:
Dim db As Database
Dim rst As DAO.Recordset
Dim pnt As Object
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * from [Warranty Performance KPI Chart]
WHERE [Dealer] = '" & Me!Dealer & "'")
With rst
.MoveFirst
For i = 1 To Me.Controls("WP").SeriesCollection(Dealer).Points().Count
Set pnt = Me.Controls("WP").SeriesCollection(Dealer).Points(i)
Select Case !Dealer
Case Is > 100
pnt.Interior.Color = vbRed 'set colour of bar
pnt.Border.Color = vbRed 'set colour of bar's border
'Me.Controls("WP").SeriesCollection(Dealer).DataLabels(i).Font.Color = vbRed
Case Is < 100
pnt.Interior.Color = vbYellow
pnt.Border.Color = vbYellow
'Me.Controls("WP").SeriesCollection(Dealer).DataLabels(i).Font.Color =
vbYellow
Case Else
End Select
.MoveNext
Next i
End With
Set rst = Nothing
Set db = Nothing
It loops through the code once, but on the 2nd run it errors with 'no
current record'. pnt doesn't seem to contain my field names either so unsure
if it is getting the recordset data properly, although it does run through
the code once?
Any ideas, thanks
Sue
Rob Parker said:Final follow-up, for any-one else following this thread, or searching in
future:
I fixed Linda's problem. The problem with the code I posted originally was
that it was designed for a chart displaying multiple records (all records in
the chart's RowSource); Linda had a chart for each record in her report's
dataset, linked via a PersonRef field. The solution was to replace the
line:
Set rst = db.OpenRecordset(Me.Controls(ChartName).RowSource)
with
Set rst = db.OpenRecordset("SELECT * FROM query1 WHERE PersonRef = '" &
Me.PersonRef & "';")
(note that PersonRef is a text datatype)
Additionally, the loop construct is not required since there is only one
record for each chart, and .Points(i) can be replaced with .Points(1). The
..SeriesCollection() index is the name of the field from query1, in this case
..SeriesCollection("Social")
Rob
Rob said:Hi Linda,
This seems to be getting us nowhere. I rarely do this, but I'm
prepared to do so now if it will help. If you send me your database
(the .mdb file - and if you're using Access 2007, you'll need to
convert it to an earlier format), I'll check it out and see what I
can do. Email it to me at the following address WITHOUT THE OBVIOUS
SPAM TRAPS: (e-mail address removed)
You can send a stripped down version with only the essential objects,
and only a few sample records in the underlying table(s) if you have
privacy (or other) problems with your complete data.
Rob
LindaBee wrote:
Hi Rob
I think I am a lost cause it is still not working now I will try for
the last time if it does not work then I will stop wasting your time.
Here is what I have a table with 10 colummns which will connatin
values on the bar and I have created a query to slect the 10 columns
wnat I would like the Query is called Query1 1 of the fields is
called Social and I wnat to try that one first now should the field
name be Social or Query1 and the graph is called RAS1
If I use a piece of code as an example have I got the this in the
right order For i = 1 To
Me.Controls("RAS1").SeriesCollection("Social").Points().Count
Set pnt =
Me.Controls("RAS1").SeriesCollection("Social").Points(i) Select
Case !Social
Controls should this be RAS1 (The graph name) and after
SeriesCollection should this be Social (one of the field names) or
Query1 which is the RowSource of the graph?
Thanks for your patience
Linda
:
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)?
LindaBee wrote:
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
:
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