Solved

Change backcolor of a REPORT control based on its value

Posted on 2004-08-17
11
279 Views
Last Modified: 2012-06-22
Hi,

I've got a report that in part gives the severity of each incident.

Is there any way to change the backcolor of each occurance of the control based on its value.

Each report will contain mutiple severities so the backcolour will be different for different records on the same report.

Thanks
Mandy
0
Comment
Question by:MandyC
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:solution46
ID: 11818392
Mandy,

I had a very similar problem with Access reports a while ago, and never did find a way to make a control have different colours for different fields. In the end, I used a workaround (or cheat, if you prefer!)...

Instead of having one control to show the severity (e.g. txtSeverity), use a number of different controls (e.g. txtSeverity1, txtSeverity2, etc.). Set the forecolour of each of these controls to the required colour, leave the background as transparent and stack them up on top of each other. In the query behind the report, add one field for each control, along the following lines (assumes your field is called 'Severity' in a table called 'Incident').

SELECT
    [ID],
    [Other Fields],
    IIf([Severity] = 1, "1", "") AS [Severity1],
    IIf([Severity] = 2, "2", "") AS [Severity2],
    IIf([Severity] = 3, "3", "") AS [Severity3]
FROM tblIncident
WHERE whatever...

In the report, point txtSeverity1 at field 'Severity1', txtSeverity2 at field 'Severity2' and so on.

One more tip: it is probably worth-while setting up the report with the Severity controls next to each other until it is all working as you want it to, then move the controls to the same location. Otherwise, it is a pain to change the properties of the controls as you can never easily select the one you want!

The result may not be perfect (it hasn't changed the background colour as you requested) but it should be sufficient to highlight incidents of different severities.

Hope this has been helpful,

s46.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11818473
Mandy & S46,
    One thing that may work for you is to set the control's "Conditional Formatting" properties, if you right click on your control and you select "Conditiional" Formatting" it will let you format that control when the record matches your criteria.
    I used to use a similar method to you s46, til yesterday I found out about conditional formatting, and now plan to start making use of it more. The only problem with it that I have found is that it is limited to 3 conditions (well, in a way 4 in that you can set 3 and then one for if the control does not matche the three that you set).
0
 
LVL 4

Accepted Solution

by:
Colonel32 earned 250 total points
ID: 11818518
In the reports VBA module, use the top-left drop-down to expose the events for the 'Detail' object. Then use the drop-down on the right to select the desired event - in your case the 'Format' event. I believe a stucture like the followiing is what you are after:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Me.ControlName
        Select Case .Value
            Case 1: lngColor = vbBlue
            Case 2: lngColor = vbRed
            Case Else
        End Select
       
        .Properties("Backcolor") = lngColor
    End With
End Sub
0
 
LVL 3

Author Comment

by:MandyC
ID: 11818527
Thanks for the suggestion but I've played around further and think I've cracked it anyway.

I've added this to the Format event in the detail section of the report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
getsev = Severity.Value
Select Case getsev
Case 10
colour = RGB(128, 0, 0)
Case 20
colour = RGB(255, 0, 0)
Case 30
colour = RGB(255, 116, 0)
End Select
Severity.BackColor = colour
End Sub

So far it appears to work - hope it helps with your situation and once again thanks for the post

M
0
 
LVL 3

Author Comment

by:MandyC
ID: 11818540
Colonel32

Thanks - you post looks similar to what I figured in the end.

M
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 3

Author Comment

by:MandyC
ID: 11818552
Will,

I can't see a conditional formatting option (I know is exists in excel) and I although I only showed 3 examples in my solution post I need more than that in real life.

M
0
 
LVL 4

Expert Comment

by:Colonel32
ID: 11818556
Oops, quick edit from test environ delivers errors :p


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngColor As Long

    With Me.Flags
        Select Case .Value
            Case 1: lngColor = vbBlue
            Case 2: lngColor = vbRed
            Case Else: lngColor = vbWhite
        End Select
       
        .Properties("Backcolor") = lngColor
    End With
End Sub
0
 
LVL 4

Expert Comment

by:Colonel32
ID: 11818574
Thanks for the nod! :)
0
 
LVL 9

Expert Comment

by:solution46
ID: 11818614
Mandy,

glad you got your problem solved - %$£&** experts, doing things properly!!!

Cheers for the tip to you and the Colonel!

s46.
0
 
LVL 3

Author Comment

by:MandyC
ID: 11818645
- before trying this make sure you set the background style to normal (as opposed to the default transparent) - that confused me for a bit!

M
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11818671
Glad you got it working. Sometime the unconventional works best. 2 thumbs up for thinking outside the box!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now