Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

ListView & Access

ListView & Microsoft Access - Part 3 - (Conditional Formatting) by ZmrAbdulla
Posted: 14 Aug 05 (Edited 14 Aug 05)

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) Freight Less than $100.00 will be green color
2) freight Between $100.00 & $500.00 will be in blue
3) Freight More than $500.00 in Red & Bold

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


Private Sub FormatListView()
Dim Item As ListItem
Dim Counter As Long
Dim FreightAmount As Currency
' Set the variable to the ListItem.
For Counter = 1 To Me.ListView1.ListItems.Count
    Set Item = Me.ListView1.ListItems.Item(Counter)
    ' Set the variable to the Freight
    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
        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
         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

           'Make font bold
            .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
End Sub
To clear the formatting you can either Refill the ListView by


Call FillOrders
or make all rows to black color


Private Sub ClearFormatListView()
    Dim Item As ListItem
    Dim Counter As Long
    Dim FreightAmount As Currency
    ' Set the variable to the ListItem.
    For Counter = 1 To Me.ListView1.ListItems.Count
        Set Item = Me.ListView1.ListItems.Item(Counter)
        With Me.ListView1
            ' Set them to black
            .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
            ' make font normal
            .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
End Sub

Download Sample(178 kb zip) [Access 2000 file format (696 kb)]

Hope this helps

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close