A Tool for Building Custom AutoFormats
In the last post, I discussed the new AutoFormats in Access 2007. In this post, I'll describe more about how they work and provide a tool that allows you to create your own formats and customize the ribbon gallery of formats.
AutoFormat Storage
Access 2007 stores AutoFormat information in two different locations, depending on whether those formats have been customized or not. Un-customized formats are stored in tables in an Access 2007 format database that is installed with Office and is shared across all users who have no customized AutoFormats. The file is located at:
%PROGRAMFILES%\Microsoft Office\Office12\ACCWIZ\ACWZUSR12.accdu
If a user has used the AutoFormat Wizard to add, edit, or delete an AutoFormat, then Access will create a copy of the AutoFormat file for that user. From that point on, all AutoFormat data for that user is stored in their personal database, which is at:
%APPDATA%\Microsoft\Access\ACWZUSR12.accdu
This is a similar scheme to the way AutoFormat information was stored in Access 2003, but the table schema has changed for Access 2007 formats, so it is not possible to reuse a version of the 2003 database to retain existing AutoFormat settings.
AutoFormat Functionality
The AutoFormat engine works by applying a single format per control type per section. This means, for example, that all the textboxes that appear in the details section of a report will get the same background color, font face, etc. as one another, but that these settings may all be different than the textboxes in the Header or another section. The AutoFormat database simply lists all of the property settings for each of the control types for each of the sections. There are four key tables in the database for these settings, along with other tables for things like supporting functionality, localization, and support for other features. They four key tables for formatting are:
- tblStyles - this is a top-level style listing, normalized to style level data like the name of the style and the picture to display. Note that pictures are stored in the Access internal "Picture Data" format which can be set and retrieved using the PictureData property on images or form and report objects.
o tblSectionStyles – Each style has a list of section styles, being the appearance for each section (page header, detail, form footer, etc…). Formats included here are section background color and alternating background color.
§ tblControlStyles – For each section, there is a list of control styles, with 0 or 1 entries for each control type. Control styles have the bulk of the interesting formatting to do with text, like Bold, Italics, Underline, as well as shape appearance, like Border Color, Back Style, Special Effect, etc…
§ tblLayoutStyles – Formatting for layouts is also specified on a per-section basis. There can be 0 or 1 format entries per section for both Stacked and Tabular layouts. Layout formatting is mostly to do with gridlines. For example, all stacked layouts in the detail section can have a ‘Both’ grid style, while all Tabular Layouts have the ‘Vertical’ style.
AutoFormat Editing Tool
The format of these tables inside the database was never meant to be human readable, and can be pretty terse to browse. To make life easier for our own internal editing, we assembled a little tool (an Access database, of course) for viewing, adding, and editing AutoFormats for a given instance of the Acwzusr12.accdu file. If you would like to play around with AutoFormats a little bit, this tool will make that easier. Keep in mind that this is, something we just threw together to work with the rather complex AutoFormat data more easily. The code is pretty rough for a sample, without extensive comments or robust error handling, but it works well for reading and editing AutoFormat data, and what’s more, the tool contains updated AutoFormat data with a number of bug fixes we’ve made since Beta 2.
The tool is available here and is a 2m zip file, so will take a little while to download. This tool will also be part of the Access 2007 Developer Toolkit and will be both polished and contain the final AutoFormat settings at that point. The file simply contains an Access database, which you can save to your machine and open. You'll need to either save the file to a trusted location on your machine or manually enable VBA for the file to work.
Opening the tool brings up the following dialog:
(Click image to enlarge)
Clicking the first link "Step 1: Add / Edit AutoFormats" brings up a large dialog with a list of all of the formats and each of the settings for each of those formats:
(Click image to enlarge)
This is just a standard Access form, so you can filter or sort it as necessary. There is a separate record for the both the Form and Report settings for each format, and then each of the settings for each control and section is laid out below. The AutoFormat property called "Gallery Index" at the top determines where this format goes in the AutoFormat Gallery shown in the last post. This doesn't update the picture in the ribbon (to do that you need to update the ribbon XML file, which we'll cover later) but this is an easy way to get your format on the ribbon.
After you have updated the styles as you like, including adding or deleting any additional styles, you'll need to regenerate the AutoFormat tables. The first setup is to click the compile button, which runs a series of queries and repopulates all of the AutoFormat tables:
You can optionally then preview the styles, with some built-in UI. This is less of a visual preview and a set of reports to allow you to verify all the settings:
(Click image to enlarge)
There is a report that verifies that there are no missing styles, which you can access by clicking the Verify button:
At this point, the AutoFormat database has been rebuilt, and you simply need to publish it to the correct location on your machine. You can do this from the initial dialog. First you click the Browse hyperlink to set the file's location (this should be defaulted for you, and all you have to do is click OK). Then click Publish and you're done.
(Click image to enlarge)
Next Time
The next big post will be about using the new Data Collection feature to add or update data through emailed forms. Those forms can either be plain HTML that work on practically any email client or InfoPath forms that provide a great user experience for Office 2007 users.
