• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Trade Show Database New.. Question on user of row colors with a List Box in Access 2007

Hi Everyone,

I have redesigned a new database now for the Tradeshow which will use a List Box.  I have a row color issue using the List Box in Access 2007.  

I am using a sub routine called Product_click()

I have this VBA Code:   If Product.Column(1) = "VAP" Then
        If Product.Column(4) = 0 Then
            Product.BackColor = HexColor("#ED1C24")
        ElseIf Product.Column(4) > 0 And Product.Column(4) < 12 Then
            Product.BackColor = HexColor("#FFF200")
        Else
            Product.BackColor = HexColor("#FFFFFF")
        End If
    Else
        Product.BackColor = HexColor("#FFFFFF")
    End If
   
End Sub

I have attached my database for review.  The database is working like I want it to, but when the Product.Column(4) = "0"  I want the red color for the row to appear and not the entire List box be red.  I want also the same thing to happen for Product.Column(4)  > 0 and < 12 be "yellow'.  I just want the row to turn "yellow" and not the entire list box.

My question, is there a way to just color the row red when the Product.Column(4) = 0 and
the Product.Column(4) > 0 and < 12 be yellow.

What VBA coding would do this?  I just want the row to change and not the entire List Box?


jjc9809
TradeShow-NEW-.accdb
0
jjc9809
Asked:
jjc9809
2 Solutions
 
peter57rCommented:
Can't be done with a listbox.

To get this effect you would need to use a continuuous subform that lists the products and use Conditional Formatting on the controls on that subform.
0
 
Jeffrey CoachmanCommented:
Agree with Pete.

Unfortunately if you really need a Listbox (becuase you need multiple/non-contiguous selections) then you are out of luck with colors.

Is a using Color a hard requirement? (suppose a user is colorblind?)
(Because again, ...it is just not possible to easily get colors in a listbox...)

Another option would be to use Text as a visible indicator
The Source for the listbox might be something like this:
    SELECT Field1, Field2, IIF(Field1=0,"ZERO","NOT ZERO") AS Result
    FROM YourTable
..obviously you would have to use a nested IIF if you had multiple conditions...
But I am sure you get the idea...

But again, if you just need to "see" the list, then use a continuous form as Pete suggested.

JeffCoachman
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now