Setting Column-Specific Colors in Microsoft Access List Boxes and Combo Boxes
How to use the Format property of a query or table field to set custom colors for columns in a Microsoft Access list box or combo box control.
Did you know that you can set custom colors for specific columns in list boxes and combo boxes in Microsoft Access?
Karl Donaubauer covered the details in his "Baker's Dozen of Tips & Tricks." It's tip #13 and begins at the 40:15 mark of the presentation:
Here are the notes from Karl's slide:
In the Format property of tables and queries, you can specify colours. These colours are also inherited in list and combo boxes. This enables coloured highlighting of individual columns or values.
Restrictions
• version-dependent/moody: works in 97, 2007, 2010, 2013, in 365 in list boxes only if primary key is present in the query, in combos only with an (arbitrary) setting in the Format property of the combo (e.g. @)
• only works with text and memo fields
• only works with tables and saved queries, SQL in the data origin (usually) does not remember formatting
• possible colours: black, blue, green, cyan, red, magenta, yellow, white
Karl shows a sample list box with the columns colored:
Making it Work: The Format Property
To make this work, you use the color names as part of the field's Format property:
Here are the formats that Karl used in his example above:
That_Is
Source: IIf([Remuneration]>=1000,"Expensive")
Format: @[Red];"Cheap"[Green]
I_Think
Source: Switch([Rating]=1,"*§$%&*",[Rating]=5,"♥")
Format: @[Red]
Country
Source: Country
Format: @;"missing"[Magenta]To download Karl's sample database, visit the resources page for the presentation on Colin Riddington's website: A Baker's Dozen of tips, tricks and tools in Access - Karl Donaubauer.
Richard Rost's Take
Access Video King Richard Rost covered this same topic in a video last year:
As is typical of Richard's style, he takes you through the process step-by-step.
External references
Colin Riddington
