Conditional Formatting in Access report bu using VBA code (1 Viewer)

jp2468

Registered User.
Local time
Today, 18:43
Joined
Sep 30, 2010
Messages
17
Hi,

I tried to build a reprot which change one of number fields color depend on another Category field at the report and number field itself.
If there is Category A, then number field will change color at certain number range.
If there is category B, then the number field will have different number range to change color.

Below is my code, however, it only works for one of Cases. Please help, thanks a lot.

Code:
Private Sub Report_Load()
' This subroutine demonstrates the use of FormatCondition objects
' to add formatting to a text box. It also illustrates ways you can
' extend the functionality of the three conditional-format limit
' allowed in the FormatConditions collection.
'
' Author: Frank C. Rice
'
Dim objFrc As FormatCondition
Dim lngRed As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim lngYellow As Long
 
' Set up background and foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(34, 139, 34)
 
' Depending on the user's option selection, format the txtResult
' box.
Select Case Category.Value
 
 
Case "A"
' Refer to each format condition by its index.
 
' Remove any existing format conditions.
Me![rptHoursLeft].FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
' collection.
 
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acLessThan, 0)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acBetween, 0, 25)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acGreaterThan, 25)
 
With Me![rptHoursLeft].FormatConditions(0)
   .BackColor = lngRed
   .FontBold = True
   .ForeColor = lngBlack
End With
 
 
With Me![rptHoursLeft].FormatConditions(1)
   .BackColor = lngYellow
   .FontBold = True
   .ForeColor = lngGreen
   .FontUnderline = True
End With
 
With Me![rptHoursLeft].FormatConditions(2)
   .FontBold = False
   .FontItalic = False
   .FontUnderline = False
End With
 
Case "B"
 
' Remove any existing format conditions.
Me![rptHoursLeft].FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acLessThan, 0)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acBetween, 0, 95)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acGreaterThan, 95)
 
With Me![rptHoursLeft].FormatConditions(0)
   .BackColor = lngRed
   .FontBold = True
   .ForeColor = lngBlack
End With
 
With Me![rptHoursLeft].FormatConditions(1)
   .BackColor = lngYellow
   .FontBold = True
   .ForeColor = lngGreen
   .FontUnderline = True
End With
 
 
With Me![rptHoursLeft].FormatConditions(2)
   .FontBold = False
   .FontItalic = False
   .FontUnderline = False
End With
 
 
Case "C"
 
' Remove any existing format conditions.
Me![rptHoursLeft].FormatConditions.Delete
 
 
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acLessThan, 0)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acBetween, 0, 5)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acGreaterThan, 5)
 
 
With Me![rptHoursLeft].FormatConditions(0)
   .BackColor = lngRed
   .FontBold = True
   .ForeColor = lngBlack
End With
 
With Me![rptHoursLeft].FormatConditions(1)
   .BackColor = lngYellow
   .FontBold = True
   .ForeColor = lngGreen
   .FontUnderline = True
End With
 
 
 
With Me![rptHoursLeft].FormatConditions(2)
   .FontBold = False
   .FontItalic = False
   .FontUnderline = False
End With
 
 
End Select
End Sub
 
Last edited by a moderator:

boblarson

Smeghead
Local time
Today, 15:43
Joined
Jan 12, 2001
Messages
32,059
Next time please use code tags for any code over a few lines.

 

ChrisO

Registered User.
Local time
Tomorrow, 09:43
Joined
Apr 30, 2003
Messages
3,202
Private Sub Report_Load() fires once.
If Category has a value it only has one value.
Select Case Category.Value can only select one Category.
If it does select a Category then only that Category is formatted.

Chris.
 

jp2468

Registered User.
Local time
Today, 18:43
Joined
Sep 30, 2010
Messages
17
Thanks, Chris,

Would you give me any idea about which function or loop can make all the Category looked, and correct color returned for different category?

Thanks again.
 

ChrisO

Registered User.
Local time
Tomorrow, 09:43
Joined
Apr 30, 2003
Messages
3,202
Why are you even trying to use that code?
 

jp2468

Registered User.
Local time
Today, 18:43
Joined
Sep 30, 2010
Messages
17
Hi, Chris,
If there is a way don't need to use code, it will be great.
However, I didn't see the Condition Format at Access 2007 report design view offer me to do color change depend on two fields condition change.

Thanks.
 

ChrisO

Registered User.
Local time
Tomorrow, 09:43
Joined
Apr 30, 2003
Messages
3,202
The code by Frank Rice you posted is inappropriate for your needs.

Have a close look at this and see if it makes any sense.
Think in terms of colours applied to Under, Normal and Over range.
Don’t expect it to work via copy/paste…
Code:
Option Compare Database
Option Explicit


Private Const conRed    As Long = vbRed
Private Const conGreen  As Long = vbGreen
Private Const conWhite  As Long = vbWhite
Private Const conBlack  As Long = vbBlack
Private Const conYellow As Long = vbYellow


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Select Case Category
        Case "A"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 25:   SetOverRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
        Case "B"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 95:   SetOverRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
        Case "C"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 5:    SetOverRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
    End Select

End Sub


Private Sub SetUnderRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conRed
        .ForeColor = conBlack
        .FontBold = True
        .FontUnderline = False
        .FontItalic = False
    End With

End Sub


Private Sub SetNormalRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conYellow
        .ForeColor = conGreen
        .FontBold = True
        .FontUnderline = True
        .FontItalic = True
    End With

End Sub


Private Sub SetOverRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conWhite
        .ForeColor = conGreen
        .FontBold = False
        .FontUnderline = False
        .FontItalic = False
    End With

End Sub

It’s 1:20 AM here so I’ll try to explain a little later.

Post back with as much detail of what you require, not how you wish to achieve it.

Chris.
 

jp2468

Registered User.
Local time
Today, 18:43
Joined
Sep 30, 2010
Messages
17
HI, Chris,

Thanks so much for working at midnight to answer my question. I am much appreciated.

I figured out at the code I posted. I just need to stick on the same forecolor, or background color within different category and conditions.

I will look at your code too to learn something from you.

Thanks again.
 

ChrisO

Registered User.
Local time
Tomorrow, 09:43
Joined
Apr 30, 2003
Messages
3,202
The data in the code supplied appear to be broken up into 3 value ranges, Under, Normal and Over.
Therefore three Subroutines are created, one for each formatting range.
The colours available for each formatting range are derived from constants.

Each Category, A, B, C… is divided into its three ranges and calls the appropriate subroutine for formatting.

If you need more Categories, example D, E, F… then add those to the Select Case Category.
If a Category requires more than three value ranges then create a new formatting subroutine and add the call to the Category Case.

Example:-
Code:
Option Explicit
Option Compare Text


Private Const conRed    As Long = vbRed
Private Const conGreen  As Long = 4227072    [color=green]'<  Dark Green not available as a VB constant[/color]
Private Const conWhite  As Long = vbWhite
Private Const conBlack  As Long = vbBlack
Private Const conYellow As Long = vbYellow


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Select Case Me.txtCategory
        Case "A"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 25:   SetOverRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
        Case "B"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 95:   SetOverRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
        Case "C"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 5:    SetOverRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
        Case "D"
            Select Case Me.rptHoursLeft
                Case Is < 0:    SetUnderRange rptHoursLeft
                Case Is > 5000: SetOverRange rptHoursLeft
                Case Is > 4000: SetHighRange rptHoursLeft
                Case Else:      SetNormalRange rptHoursLeft
            End Select
        
    End Select

End Sub


Private Sub SetUnderRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conRed
        .ForeColor = conBlack
        .FontBold = True
        .FontUnderline = False
        .FontItalic = False
    End With

End Sub


Private Sub SetNormalRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conYellow
        .ForeColor = conGreen
        .FontBold = True
        .FontUnderline = True
        .FontItalic = True
    End With

End Sub


Private Sub SetOverRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conWhite
        .ForeColor = conGreen
        .FontBold = False
        .FontUnderline = False
        .FontItalic = False
    End With

End Sub


Private Sub SetHighRange(ByRef ctlThisControl As Control)

    With ctlThisControl
        .BackColor = conGreen
        .ForeColor = conYellow
        .FontBold = True
        .FontUnderline = False
        .FontItalic = False
    End With

End Sub

There is a small Access2003 demo attached.

Chris.
 

Attachments

  • CategoryReport.zip
    16.1 KB · Views: 1,312

jp2468

Registered User.
Local time
Today, 18:43
Joined
Sep 30, 2010
Messages
17
Hi, Chris,

Thanks so much for your help. I am much appreciated your time and hardwork.
 

Users who are viewing this thread

Top Bottom