Reduce repetitive formatting for Access objects

by Guest Contributor | May 07, 2007 7:00:00 AM

Tags: Databases, Microsoft Access, Susan Harkins, default property, text box control, box control, background color

Takeaway: If you find yourself constantly making formatting adjustments to Access objects on the fly, you're bogging yourself down unnecessarily. As Access expert Susan Harkins explains, a couple of tricks can save you tons of time: Either change control defaults for a particular form or report or customize a form or report and turn it into a template.

This article is also available as a PDF download and a gallery.

Objects come with a set of built-in properties that determine how they look and behave. As you create new forms, reports, and controls, you can adjust an object's default properties as required. But often, you'll find that objects require the same changes. For instance, you may want to use a font other than the default throughout your database. Changing the font for every new control on every form and report is inefficient. There are two ways to reduce your work when the default formats Access offers aren't adequate:

• Change control defaults for the current form or report.
• Use a custom form or report as a template.

Create a custom control

If a control's default formats aren't adequate, you can change the default. There's a trick to the process, and it's not easy to discern. But it's easy to implement once you know how. Let's apply this trick to change a control's default background color:

1. Open a blank form in Design view.
2. Insert a text box control. Most likely the default background color is white, but it doesn't matter.
3. To change the default background for subsequent text box controls, click the Text Box control in the Toolbox, but don't click inside the form.
4. Click the Properties button. Notice that the resulting window's title bar identifies the current control as the Default Text Box.
5. In the Properties sheet, change the Back Color property (see Figure A) to any color other than the current color. (Click the Builder button to display an easy-to-use palette.) At this point, you can change as many properties as necessary.
6. Click inside the form to insert a new text box control. This control's background color isn't the same as the first, as you can see in Figure B, but you expected that.
7. Insert a third text box control, as shown in Figure C. Its background color is the same as the second control's. The form remembers the change you made before inserting the second control.

Figure A

Change a default property, such as the control's Back Color, in the Default Text Box Properties window.

 

Figure B

The new control's background color is different from the first control's.

 

Figure C

Changing a property before you insert a control changes that control type's default property.

If you change a control's property before inserting it, you change the default property for that particular control type for that form (or report). Keep in mind the following when temporarily changing default properties:

• The default is valid for only the specified control type. Changing a text box control's default property won't affect a list box or a label control -- only the text box control will apply the temporary default.
• The default remains in effect until you change it by repeating the process or closing the form or report without saving the new default.
• The change affects only the current form or report.
• You can change multiple properties for a single control type at the same time.

If the form or report already has a control with all the appropriate settings, select the control and choose Set Control Defaults from the Format menu. Doing so has the same affect as the above process.

If you want to use a custom control in other forms, use the copy and paste method to copy the formatted control from one form to another. In the new form, with the formatted control selected, choose Set Control Defaults from the Format menu to update the control defaults for that form.

Customize database-wide defaults

For the sake of consistency, formats usually apply to objects throughout a database. Although you can copy custom formats from one form to another, doing so is inefficient. To apply custom formats database-wide, create templates. To create a simple form template, complete the following steps:

1. Open a new form in Design view.
2. Change the Detail section's Back Color property.
3. Insert a text box control and change its Border Color to a color that contrasts with the Detail section's new background.
4. Set the control's Border Width to 5 point (so you can easily see the difference).
5. Select the text box and choose Set Control Defaults from the Format menu to set the default properties shown in Figure D for subsequent text box controls.
6. Save the form, naming it Template, as shown in Figure E. (You must name the form accordingly for this technique to work.)

Figure D

Set a form and its controls defaults to create a database-wide template.

 

Figure E

Be sure to name the formatted form (or report) Template.

Using the template couldn't be simpler. When you're ready to create a new form, click the Forms shortcut in the Database window and then click New on the Database toolbar or double-click the Create Form In Design View shortcut, just as you normally would. The new form's custom background color and default text box control will match those in Template.

You can create a report template the same way. All subsequent forms (and reports) will begin with Template. At any time, you can update the form defaults by redesigning Template. Creating a template will not change existing forms.

To reset the database's template option back to Normal, choose Options from the Tools menu and click the Forms/Reports tab. Enter Normal in the Form Template field, as shown in Figure F. Doing so won't destroy the Template form object, but it will reset the template form the database uses for new forms.

Figure F

Reset the database form and report template.

When you need multiple templates, create the multiple form and report objects, using a descriptive name to save each. Instead of using a wizard or shortcut to create a new object, use the appropriate template. After opening the object template, rename it immediately. Make changes only to the newly named copy. That way, the template remains a template and you can quickly apply the template's formats to each new object as needed.

You may find that you need the same custom formats for more than one database. When this happens, just import your templates and customized objects.

 

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@setel.com.