You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's guest blogger is Pavlo Pedan of ARGO Business Corp. He has 15 years of experience with Access and has a great site of tips at http://sites.google.com/site/msaccesscode/.
As we all know, a List Box control has a Fore Color property. It sets the fore color for column values and headers simultaneously. Also, we know that table and query fields have a Format property, where color formatting can be set. For example, you can format a number field as 0.00[Green].
As stated in Access 97, 2003 and 2007 help, "If you set a field's Format property in table Design view, Microsoft Access uses that format to display data in datasheets. It also applies the field's Format property to new controls on forms and reports."
So when creating a new Text Box control, the Text Box inherits the underlying field's Format property. However, List Boxes and Combo Boxes can also inherit this color formatting. And, it works for existing controls as well as new ones. You can change the Format property for a table field, and the new color will be inherited by an existing control (Text Box, List Box, or Combo Box).
The strange thing is, the List Box control has no Format property, and the Format property for the Combo Box control is not changed by the Format property of the underlying field. See a sample database here for some examples.
Using color formatting for list boxes and combo boxes definitely makes an application more attractive. One can set different columns into different colors, set different colors for header and items, and use a type of conditional formatting for empty values. For example, the Format property for Field2 in the picture above is: [Blue];@;"Empty"[Magenta]. This formatting overrides the Fore Color property of the control.
Such formatting is applicable not only for underlying tables, but for queries as well. Therefore, by choosing different queries for different List Boxes, we can have different sets of colors while referencing the same table.
However, some limitations apply. The above only seems to work for Access 97 and 2007 (and 2010?) but not for 2003. Also, it seems to work only for Text and Memo fields.
Have you experimented with this? If so, please post a comment!
Thank you, Pavlo
Comments: (14) Collapse
I have not formatted with this as I never saw the need but I appreciate your info and will keep it in mind now that you have brought it out.
Excellent! It also works in Access 2010 beta. Alan
wow, really cool trick -- thanks! Warm Regards,
Crystal * (: have an awesome day :) *
Nice share Pavlo! How about Alternating row color for listbox and combo? is there a formatting trick's like that? for A 97-2010? Thanks ERwin
Hi ERwin, Color formatting is applied to a field of table or query, so only columns can be colored using this approach.. You might want to consider using subdatasheet with conditional formatting to have different row colors. Regards,
Pavlo
Can we apply this for aligning numbers: let say right align for currency format.
Hi Misha, I think not..
For a Listbox, try to convert numbers to string and add extra spaces at the beginning of that string. The following function will do this conversion: Function fRightAlign(Nmb As Currency, LineLength As Integer) as String fRightAlign = Space(LineLength - Len(CStr(Nmb))) & CStr(Nmb)
End Function Additionally you must use Courier-type font to have proper alignment. Hope this helps
Thank you Pavlo. Interesting, will something changes in Access 2010?
This is a GREAT, GREAT Post. . Thanks my friend . . Edwin
Misha, If you still want to have right-alignment in a ListBox, try the following trick: 1. Create a Combo Box using the same Row Source (table or query), that you would use for a List Box.
2. Set Text Align property for that ComboBox - Right (List Box doesn't have such property).
3. Convert ComboBox to ListBox (Right-Click on ComboBox -> ChangeTo -> List Box) However, if you have multiple columns in a List Box, all of them will be right-aligned.
The above works for Access 2007. I do not have Access 2010 installed, so I'm not able test that.. Take care
I'm a developer and like colored table cells or color columns so I can designate required fields for end users in a table datasheet where I have line items would be really awesome. I don't ever need the whole grid one color, but I want the column headers to be in red, or all the cells in that column to be red color, but I don't understand what this blog says? Is it about that or is it about conditional formatting? What is this blog post about?
Dennis: Yes, it's about conditional formatting on a table level. You can download a demo project to understand the trick. Pavlo: Nice post, thx for sharing.
Can this be only done with "null"? I have a listbox with two dates. They represent from and to dates. Could I make the from date "green" and the to date "red" but only in the first and last rows of the lisybox?
Hi, neil tetlow
I don't think this is possible.. Format property supports separate formatting only for Null and zero-length string.
See the following link: office.microsoft.com/.../format-property-text-and-memo-data-types-HA001232747.aspx
Consider using Microsoft ListView ActiveX control instead. There are some other third-party controls with similar functionality on the market as well.
Another option is using conditional formatting on datasheet or continuous sub-form.
Comments: (loading) Collapse