Using vba code to change the color of a form button in Access 2010

[Sally Bullard]

I have a form with a button to filter records and another to show all records. When the records are filtered I want the color of the button to change so that the user can immediately see that the records are filtered.

I changed the color on the form, noted the new color on the property sheet of the button. It is no longer a number, but text reflecting the "theme" that I am using. I tried using that text as follows:

cmdShowAll.Backcolor = "Accent 2, Lighter 40%"

No luck. There used to be numbers for the colors. Do I need to convert those theme colors back to numbers for the code? If so, how do I find them?

[sierra7]

Hi Sally
There may be something new in 2010 but the old way was to set the Background Style to Transparent and then draw a coloured rectangle behind it.

You should be able to change the colour of the rectangle via VBA but I have not gotten to grips with 'Styles' yet in the 2010 and I don't know if it gets over-written.

Best of luck!
S7

[Sally Bullard]

Thanks for the tip. While I was working on that, I looked at the VBA property sheet for the control and it actually had the color number codes. So, I used that to set the properties with the appropriate number codes and it worked great. Thanks for taking the time to help me out!

[NeoPa]

In truth the solution S7 was suggesting can be used, but it is only really necessary when being clever with conditional formatting. There is no reason in your situation to introduce separate elements into the mix when it can all be done with the existing control.

[sierra7]

Hi
Sally, you are correct, BackgroundColor for a button now works as expected.

Access 2003 had a background colour attribute but it didn't work, hence the trick if you really did want to change the colour, (and why I thought you had a problem in the first place).

I checked before posting and found that it still does not work in Access 2010, but I was using an 'mdb' type database. Since your post I've checked and agree that it now works as expected with a 'accdb' type database.

Thanks for pointing this out!
S7

[NeoPa]

My mistake. I was also referring to Access 2003 but overlooked the fact (as S7 so clearly points out) that buttons do not have the facility to set a background colour. My mistake.

S7 has now clarified the situation fully though. Trust him. He knows what he's talking about (which I didn't this time :-().