Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Microsoft: Access Forms FAQ

ListView & Access

ListView & Microsoft Access - Part 3 - (Conditional Formatting)
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

CODE

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
        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

           '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
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
    ' 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
    Me.ListView1.Refresh
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