Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21

    Unanswered: Conditional formatting based on combobox selection

    I have a form with a combo box that has about 200 values. Here's the SQL for it:

    SELECT DISTINCT [tblApplications].[Application], [tblApplications].[AffectsAvailability], [tblApplications].[CustomerFacingApp] FROM tblApplications ORDER BY [tblApplications].[Application];

    'Application' is the value that is saved from the box. I want to do some conditional formatting based on the other two values I brought into the query, AffectsAvailability and CustomerFacingApp. They are both true/false fields. I want to make the fore color one color for one and another for the other if one or the other is true. I'd like to change the font to a third color if both values are true. I saw elsewhere here that combo boxes don't support conditional formatting but I'm thinking I might be able to do it with the true/false field in the SQL statement in VBA? Conditional formatting won't work for me because I'd have to put close to 50 statements in the conditional formatting dialog box and the limit is 3. I'm attaching a stripped down version of what I'm trying to accomplish.

    If I can't change the font on the combo box, I'll just put a box behind the combo box that's slightly bigger and change its background color.

    So, how do I accomplish this? Do I need those values in the query or is there another way to do it?
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Posts
    90
    This attached mod of yours is the way I'd do it.

    I've done it to change the back colour of the box depending on yes or no on either of the fields. It should be easy for you to include the the condition that if they are both yes the text should change colour.

    Note: In my example I've bound the form to the table, so have disallowed additions and deletions on the form. You should do this on yours, otherwise it could get messy. If unsure, just post.
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    I don't think you can affect the *collapsed* combo box appearance without affecting the look of the drop down list (at least not with the built-in Access combo box control). I like the box approach, which can be done without too much heartache.

    Try this code in the AfterUpdate event of your combo box
    Code:
    Private Sub cboApplication1_AfterUpdate()
        Select Case True
            Case Me.cboApplication1.Column(1) = -1 ' Yes value for availability
                Me.box1.BackColor = vbBlue
            Case Me.cboApplication1.Column(2) = -1 ' Yes value for customers
                Me.box1.BackColor = vbGreen
            Case Else
                Me.box1.BackColor = vbWhite ' otherwise, keep it white (default)
        End Select
    End Sub
    Lemme know if this works out for you...
    Kael Dowdy, MCSD, MCDBA

  4. #4
    Join Date
    Feb 2004
    Posts
    90
    I knew my way would be pants!

    JBWebDatabase, use kaeldowdy's method. It's much more elegant.

  5. #5
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21
    Thanks Goldy and Kael. I tried Kael's method. It worked beautifully for the first condition (Availability, column 1) but not the second (Customer, column 2). I'll keep messing with it and check back every now and then.

    John

  6. #6
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    You're welcome. Not sure why it's not working on your end. Is it throwing an error or just not doing what you expect/want?

    Do you want to look at both fields' values at the same time or are they mutually exclusive?


    • Availability = Yes, and don't care about customer value (blue)
    • Customer = Yes, and don't care about availability (green)
    • Look at *both* availability AND customer together?

    Know what I mean?

    Quote Originally Posted by JBWebDatabase
    Thanks Goldy and Kael. I tried Kael's method. It worked beautifully for the first condition (Availability, column 1) but not the second (Customer, column 2). I'll keep messing with it and check back every now and then.

    John
    Kael Dowdy, MCSD, MCDBA

  7. #7
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21
    It's not doing what I expect/want. When I select an application that affects Availability the box changes to a beautiful shade of blue but when I select an app that affects Customers it changes to white when it should be green.

  8. #8
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    Hmmm...well, that's the way I took the original message. Could you post the modified code? I'm guessing you changed my example to the object names you have in your form. It may just be a little syntax thing.
    Last edited by kaeldowdy; 08-25-06 at 16:49.
    Kael Dowdy, MCSD, MCDBA

  9. #9
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21
    Suddenly, it works. It must have been a syntax error. I just recopied your code over my old code, changed object names, tested it out and it lit up like a Christmas tree!

    Thanks a lot!! I'll try applying it to my REAL application on Monday!

  10. #10
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    No problem; I'm glad you got it figured out!
    Kael Dowdy, MCSD, MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •