Changing Form back color in Datasheet view - Access 2007

[Nick Cooper]

I have some Forms that were created from Queries and are presented in Datasheet view. I can change the back colour in design mode but when I save changes and open the Forms they revert to the old grey color again. Is there some VBA code I can use to set the colour every time a form is opened? I've had no success so far.

[NeoPa]

The colour for Datasheet View is not relative to individual forms. Datasheet View, and its configuration, is a property of Access itself, and you will find configuring it can be done using Tools | Options and selecting the Datasheet tab.

[Seth Schrock]

I believe that in Access 2007, this would be found in File (the Office logo) > Access Options. In Access 2010 however, there isn't an option for the background color in the Datasheet tab. Not sure if Access 2007 has it there or not.

[Nick Cooper]

Thanks guys. This database has quite a few Forms. Some are converted from Queries primarily to make them programmable, but also to be able to make them intuitively recognisable (or so I thought). I wondered if this might be achievable using the BackColor property in VBA on Form loading, but your replies imply not. Thanks anyway.

[TheSmileyCoder]

Another option if it is only the backcolour of the fields you want to have coloured is to use conditional formatting. Its not something I am too fond of as it often appears to be a bit "laggy" in updating, but it might be something to consider.

[zmbd]

V2010:
Open the form in datasheet view.
You should see a context Ribbon with main tab title "FORM TOOLS" sub heading "Datasheet"
Formatting subgroup.
You can set the background colour and the alternate row colour within this ribbon.
Setting these from the properties sheet appears to just effect the single/continuous form views.

Also via vba:
give the form a module
(just something with an event - even the onload just something)
Then using the intlisense:
me.datasheet____________ will give you a list of the datasheet background etc. You can select one of these and then [F1] for more information as some of these were added starting with V2007.

For the v2003: DatasheetBackColor Property [Access 2003 VBA Language Reference] you can look at the others within the list to the left.

I'm sure there's something along the same lines for V2007/2010; however, I don't have time to hunt it down today.

[Nick Cooper]

Thanks. Your VBA solution typing in "me.datasheet.BackColor = RGB(X,Y,Z)" on opening the form worked fine!

[NeoPa]

If that works for you in Access 2007 then it must have been introduced in that version. I know it isn't available in 2003.

[zmbd]

Neopa:

Directly from the link given for 2003:

The following table contains the properties that don't exist in the DAO Properties collection of until you set them by using the Formatting (Datasheet) toolbar or you can add them in an Access database (.mdb) by using the CreateProperty method and append it to the DAO Properties collection.

[TheSmileyCoder]

Wow, talk about a deeply buried feature.

[NeoPa]

Indeed Smiley. That about sums it up.

Z. That's fantastic to know. Adding the property is not a problem. I just never knew it even could be until your comment.

[zmbd]

Ran across it quite by accident.
One of the commercial applications we were using could do this... so trying to duplicate it... thinking that the COM add-in had it buried, well, leave it to a chemist to break something apart (giggle).

[TheSmileyCoder]

/me runs off to google "Access easteregg" and "Access hidden features"

[NeoPa]

Z:
leave it to a chemist to break something apart (giggle).

I'm continually impressed by the chemists we have contributing on the site :-)