MS Access - Form - Add color to combo box drop down using Conditional Formatting

Hey,

I have a combo box in a form that have record source as two field i.e ID and Full Name. The width of ID field is set to zero to hide it. I want to color the drop down list as the record having ID from 500 to 508 should have grey back ground in drop down list, and then from 509 to 515 as green and so on, can anyone let me know how to achieve this? I have tries using conditional formatting but it's not working either i am doing something wrong or it's not working, any help?

Thank you.
LVL 1
WSStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I don't think that this is possible...the only thing you could do is to mimic it by using a small borderless form that you will place it under a fake combo box (the fake combo box should be a text box and command button )...it would take some work...
0
WSStudentAuthor Commented:
I have look on internet and people have achieve this using Conditional Formatting for combo box. In my case i am not sure how to make this working using ID as the field which is visible is Full Name and i want to apply on Full name using ID field. It can be achieve but how to trigger this is basically what i am confused about.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As John said, you can't do this.  The conditional formatting applies to the text box portion of the control, not to the list.

 You would have to use a a sub form in continuous form view along with a text control to mimic the combo control.  in that way, you could do what you want.  You would use the OnChange event of the text control to incrementally search the subform.

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

WSStudentAuthor Commented:
does can be done via VBA as i am not able to understand the other way in my case?
0
John TsioumprisSoftware & Systems EngineerCommented:
The whole concept has to be done in vba...there is no other way...
0
WSStudentAuthor Commented:
I found this somewhere, and as they mention  "Change color of item thats in a combo drop down list"  using:

You can do this with Conditional Formatting, assuming you're running Access 2000 or later. In Design View, select the combobox and goto Formatting - Conditional Formatting and then select

Field Value Is

Equal To

then enter the value that you wish to format on.

Now choose the formatting.

Click OK.

....
Isn't there a way to achieve this using this method in my combo box?
0
John TsioumprisSoftware & Systems EngineerCommented:
The only "easy" thing you can do(with VBA) is to use an Activex control named ImageCombo so that you have color indicators (images) before your info...just follow the tutorial from MS..
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Isn't there a way to achieve this using this method in my combo box?>>

 None with the native combo that I am aware of.

Jim.
0
PatHartmanCommented:
It is NOT possible to colorize the dropdown list of a combo.  WHY? Because Access keeps only one set of properties for the control.  It doesn't keep a set for each item.  All the stuff you are finding on the internet is referring to the "box" part of the control rather than the "list" part.

There is an old ActiveX control called Listview.  That might work but it is a List rather then a Combo.
https://msdn.microsoft.com/en-us/library/aa733652(v=vs.60).aspx

Be careful though. This is old technology and it might not even work in the latest version of Access.  I'm also pretty sure there is no 64-bit option.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Is this non-standard functionality a hard requirement?
Or is this something you are just "wondering about"?

As all the Experts above have stated, ...this is not something that Access can easily do.

I will throw out another option.
Since we don't know if the number "ranges" as finite or not, I would add a calculated field to the list with the actual "Name" of the range.
(This would also help so that users do not need a color legend to see what each color means )
Also note that your ranges themselves vary:
500-508=9 Records
509-515= 7 records,
...not sure if this is relevant or not.?
Finally, we don't know if the ranges need to be redefined regularly.


Create a simple public function to define the ranges:

Public Function GetRange(YourID) As String
    If YourID >= 500 And YourID <= 508 Then
        GetRange = "Red"
    ElseIf YourID >= 509 And YourID <= 515 Then
        GetRange = "Green"
    ElseIf YourID >= 516 And YourID <= 530 Then
        GetRange = "Blue"
    ElseIf YourID >= 531 And YourID <= 535 Then
        GetRange = "Yellow"
    End If
End Function

Set the rowsource of the combobox to something like this: (and adjust the column count accordingly):
SELECT tblOrders.OrderID, tblOrders.CustomerName, GetRange([OrderID]) AS Range
FROM tblOrders;

You get something like this:



Sample db is attached.

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
sample db
Database190.mdb
0
WSStudentAuthor Commented:
@Jeffrey,
Is this non-standard functionality a hard requirement?
Or is this something you are just "wondering about"?
It's the requirement of user, as the drop down is a combination of Country and Regions so they want to differentiate the two with colors.

..not sure if this is relevant or not.?
Finally, we don't know if the ranges need to be redefined regularly.
Yes the ranges are not fixed like for first 9 it will be red then for the other seven it will be grey for the next different but i can make them fix like for first 10 red then for next 10 green and so.

@PatHartman, i have Access 2016 and it's 64-bit.
0
WSStudentAuthor Commented:
Also is there any suggestion like to how to meet these user requirement in other way would also be helpful like other than coloring is there a way?
0
John TsioumprisSoftware & Systems EngineerCommented:
with my solution ...which probably bypassed it...you can even have the flag of the country....:)
0
WSStudentAuthor Commented:
@John, no no it's not bypassed, i am working on that to see will that work in my case because i am a bit confuse in your solution to how much extend it will meet user requirements, do you have any sample db with that , if yes it would be helpful if you share that.
0
John TsioumprisSoftware & Systems EngineerCommented:
sorry its inside a proprietary application i have build...but the solution is quite easy as long as you follow the tutorial...just drop on the form the control along with an imagelist....store on the imagelist the flags...populate the combobox...
0
WSStudentAuthor Commented:
but this will be image right? not the color on combo box list, am i correct?
0
John TsioumprisSoftware & Systems EngineerCommented:
Color is not possible unless you are more than proficient in API and subclass the combobox and then override the paint/render method....but i am not aware of anyone that has ever done this....i wanted this for ages after i saw a web page with a combobox exposing this functionality...but HTML is way more versatile
If you want color on the list of the combobox my initial solution about a borderless continuous form should work fine
0
PatHartmanCommented:
Color is not possible in a combo.. It is possible in an ActiveX listbox control.  However, I don't believe any of the old controls have been translated for 64-bit.

Microsoft is still recommending to NOT install 64-bit Office unless you need to support humongous spreadsheets.  there is nothing "better" about 64-bit office and you loose any 32-bit addins that have never been converted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.