Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ListView & Microsoft Access - Part 3 - (Conditional Formatting)

ListView & Access

ListView & Microsoft Access - Part 3 - (Conditional Formatting)

by  ZmrAbdulla  Posted    (Edited  )
This is the third part of FAQ on ListView & Microsoft Access

Here are the first 2 parts of the FAQ
ListView & Microsoft Access - Part 1 - (Load Data) FAQ702-6025
ListView & Microsoft Access - Part 2 (Master-Sub & Column total)faq702-6026

In this FAQ I am trying to explain how to do a conditional formatting in a Listview data.

Please see the second part(faq702-6026) to get the code that loads "QryOrders" to the ListView used in this session.
We need change the color & size of the font depending to the Freight amount.
In a continues form you can use 3 conditional formats and 1 default format. Here you can use as many as you need. Please remember increase length of code & number of conditions reduces the speed of your application.

We have three conditions in this example

1) [color green]Freight Less than $100.00 will be green color[/color]
2) [color blue]freight Between $100.00 & $500.00 will be in blue[/color]
3) [color red]Freight More than $500.00 in Red & Bold[/color]

You can use VBA recognized color numbers or normal vba format(vbBlue, vbBlack etc..)
Note: You can't change the backcolor of row conditionally.

Here is the code to format the listview
Code: 
Private Sub FormatListView()
Dim Item As ListItem
Dim Counter As Long
Dim FreightAmount As Currency
[color green]' Set the variable to the ListItem.[/color]
For Counter = 1 To Me.ListView1.ListItems.Count
    Set Item = Me.ListView1.ListItems.Item(Counter)
    [color green]' Set the variable to the Freight[/color]
    FreightAmount = Item.SubItems(4)
    With Me.ListView1
        If FreightAmount < 100 Then
            .ListItems.Item(Counter).ForeColor = 883995
            .ListItems.Item(Counter).ListSubItems(1).ForeColor = 883995
            .ListItems.Item(Counter).ListSubItems(2).ForeColor = 883995
            .ListItems.Item(Counter).ListSubItems(3).ForeColor = 883995
            .ListItems.Item(Counter).ListSubItems(4).ForeColor = 883995
        Else
        If FreightAmount <= 500 Or FreightAmount <= 100 Then
            .ListItems.Item(Counter).ForeColor = vbBlue
            .ListItems.Item(Counter).ListSubItems(1).ForeColor = vbBlue
            .ListItems.Item(Counter).ListSubItems(2).ForeColor = vbBlue
            .ListItems.Item(Counter).ListSubItems(3).ForeColor = vbBlue
            .ListItems.Item(Counter).ListSubItems(4).ForeColor = vbBlue
         Else
         If FreightAmount > 500 Then
            .ListItems.Item(Counter).ForeColor = vbRed
            .ListItems.Item(Counter).ListSubItems(1).ForeColor = vbRed
            .ListItems.Item(Counter).ListSubItems(2).ForeColor = vbRed
            .ListItems.Item(Counter).ListSubItems(3).ForeColor = vbRed
            .ListItems.Item(Counter).ListSubItems(4).ForeColor = vbRed

         [color green]  'Make font bold[/color]
            .ListItems.Item(Counter).Bold = True
            .ListItems.Item(Counter).ListSubItems(1).Bold = True
            .ListItems.Item(Counter).ListSubItems(2).Bold = True
            .ListItems.Item(Counter).ListSubItems(3).Bold = True
            .ListItems.Item(Counter).ListSubItems(4).Bold = True
            End If
            End If
        End If
    End With
Next Counter
Me.ListView1.Refresh
End Sub
To clear the formatting you can either Refill the ListView by
Code: 
Call FillOrders
or make all rows to black color
Code: 
Private Sub ClearFormatListView()
    Dim Item As ListItem
    Dim Counter As Long
    Dim FreightAmount As Currency
    [color green]' Set the variable to the ListItem.[/color]
    For Counter = 1 To Me.ListView1.ListItems.Count
        Set Item = Me.ListView1.ListItems.Item(Counter)
        With Me.ListView1
           [color green] ' Set them to black[/color]
            .ListItems.Item(Counter).ForeColor = vbBlack
            .ListItems.Item(Counter).ListSubItems(1).ForeColor = vbBlack
            .ListItems.Item(Counter).ListSubItems(2).ForeColor = vbBlack
            .ListItems.Item(Counter).ListSubItems(3).ForeColor = vbBlack
            .ListItems.Item(Counter).ListSubItems(4).ForeColor = vbBlack
            [color green]' make font normal[/color]
            .ListItems.Item(Counter).Bold = False
            .ListItems.Item(Counter).ListSubItems(1).Bold = False
            .ListItems.Item(Counter).ListSubItems(2).Bold = False
            .ListItems.Item(Counter).ListSubItems(3).Bold = False
            .ListItems.Item(Counter).ListSubItems(4).Bold = False
        End With
    Next Counter
    Me.ListView1.Refresh
End Sub

[link http://www.geocities.com/zameerabd/dl/LVConFormat.zip]Download Sample[/link](178 kb zip) [Access 2000 file format (696 kb)]

Hope this helps
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top