Remove all formatting using VBA Access 2010 database

I've just updated a .mdb to an .accdb.  Is there any way to utilize vba to loop through all forms and remove any old formatting?
John DesselleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Yes there is but I haven't attempted it.  You are not removing old formatting though, you are applying theme values.   Mostly it will be the color and font properties that need to change.  

Take a simple form and recreate it using A2010.  Then in design view, compare the settings for the various controls and sections.  You should be able to come up with a list of colors for each.  I would make a table that does the conversion since it will be far easier to change and add to.  So, you'll end up with a list of conversions:

OldColor, NewColor
#FFFFFF, Background 1
#43B3B3, Background 2
#000000, Text 1, Lighter 25%

As you are doing this, think about making it generic enough to use for other projects.  You will probably be able to sell it.
John DesselleAuthor Commented:
Is there any way to automate the process?  I have several forms I'd like to apply the theme values to.
I guess you didn't understand that I was trying to explain how to write code to automate the process.  Microsoft did not provide a tool to use and I have not seen any on the web so you will have to build your own tool if you don't wish to make the changes control by control, section by section, form by form, report by report.

the problem with the properties window of A2010 is that it mixes two kind of settings into one property (like BackColor). You see a text like "Background 1" and this is no color but the color setting of A2010 designs which goes internally into a completely different property. The same with additional texts like "lighter 10%" and so on which again goes to another property. The problem is: You need the exact syntax of the text if you want to enter that manually into the property field of the background color. If you try to change the order (like "Darker 10%, Background 1") Access throws an error. Moreover, it need to be in the current language, in my German version it is "Hintergrund 1, Dunkler 10%". Really confusing. You can only set it with the property dialog if you write it exact manually or if you select the color from the color dialog.

But fortunately, all these properties are simply accessible in the object model.

To assign the design color you can use the new property "BackThemeColorIndex" of i.e. a Textbox. -1 means, that you've assigned a direct color like #FFFFFF to the BackColor. If you assign 1 to the BackThemeColorIndex property, Access now sets the BackColor property to the color which was chosen in the current design. You see that color value if you read out the BackColor property in VBA now. As any design have different color values it doesn't help to change the BackColor property as it would not assign a value in BackThemeColorIndex and so it would be no design color anymore and Access would not change that color if you change the design.
The same the other way round: If you directly change the BackColor in VBA then the BackThemeColorIndex property will automatically change to -1 which means: No design for this control, please.

So what you can do now is, loop through all controls of your forms and set the BackThemeColorIndex depending on the desired design color. There are twelve from 0 to 11, you'll find the list in the Access help for BackThemeColorIndex.

Means for example:

Dim ctl As Access.Control
For Each ctl In Me
    If TypeOf ctl Is Access.TextBox Then ctl.BackThemeColorIndex = 0  ' Text 1
    If TypeOf ctl Is Access.Label Then ctl.BackThemeColorIndex = 2      ' Text 2

Open in new window

You must add additional code for any other type of control which supports designs.
The second part "Lighter 10%" or "Darker 20%" can be reached with the additional new properties "BackTint" (which controls the "Lighter") and "BackShadow" (which controls the "Darker"). Don't know why MS made two of them. If you set both to 100 (means 100%) you get the original color from the design, a value of i.e. 25 means 75% appears in the BackColor property setting in the dialog. So if you want to graduate any of the design colors you can add values for these properties in the loop above like you prefer. If i.e. all of your title labels in all forms are named "lblTitle" you could test for this name in such loop and add the wanted properties which you prefer for all titles of all forms.

If you want to do that with all forms you must open them in design mode in a loop and walk through the controls collection of the form object in the shown way (of course you would need i.e. "frm.Controls" instead of "Me" if "frm" was set to the form's object reference of the form you opened in design mode).

The same can of course be done with reports.

The design itself can be changed using
CurrentDb.Properties("Theme Resource Name")

Open in new window

That is a global setting for the complete database.

By the way there are more properties for the other colors also, like "ForeThemeColorIndex", "ForeShade", "ForeTint" or "BorderThemeColorIndex", "BorderShade", "BorderTint" - you would of course need to set them all if they should follow the design.



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
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.