This article is also available as a PDF download.
Access' native controls are functional without being overwhelming. However, the other Office applications don't always interpret Access controls properly. For instance, the publishing process loses check box controls and their bound data when publishing an Access report to Word. Fortunately, there are a couple of easy fixes for this limitation: Display the literal values or use Wingdings to display the check box controls in Word.
Saga of the lost control
By default, Access displays a check box control for a Yes/No field. A simple report, like the one in Figure A, uses a check box to display the Discontinued values. We based this report on the Products table in the Access sample database, Northwind. Figure B shows what happens when you publish this report to Word. (To publish a report to Word, use the Office Links button.) The check box control and data completely disappears. To fix the problem, we can either display just the data in the form of Yes and No, On and Off, and True and False or use Wingdings to display the check box graphic.
|A Yes/No field displays values in a check box control by default.|
|Word loses Yes/No values when published via a check box control.|
Display just the values
The simplest way to guarantee that Yes/No values make it to Word is to display those values in a text box instead of the field's default check box. The switch is simple. First, open the table with the Yes/No field in Design view and select the appropriate field row. Then, click the Lookup tab in the Properties panel and choose Text Box from the Display Control field, as shown in Figure C. Save the table and close it. New reports based on the table will display values, not check box controls, as shown in Figure D. When you publish the modified report, as shown in Figure E, Word displays Yes and No values accordingly.
|Changing the Yes/No field's Display Control property solves the problem of lost values.|
|Changing the Display Control property changes the way the report displays the Yes/No values.|
|Word can accommodate Yes/No values in a text box.|
Just remember that if you change the Display Control at the field (table) level, all new controls will be a text box control. You might not want that much change. Even if you do, Access won't update the controls in existing reports. You must replace check box controls with text box controls in existing reports.
You're not stuck with Yes and No; they're just the default values. At the table level, you can use the field's Format options to display True and False or On and Off instead of Yes and No. You can also change this property at the report level to avoid changing the table default for all objects bound to the same table.
Enhancing with Wingdings font
If you really want the check box controls in your Word document, you can have them. The first step is to replace the report's check box with a text box, just as you did in the last section. It doesn't matter whether you change the Display Control property at the table level or you replace the existing check box with a text box in the report. Once you have a text box in the report, you must trick Word into displaying check box controls instead of values by setting the text box control's Format property as follows:
- If you manually replaced the control, be sure to set the new text box control's Control Source to the appropriate Yes/No field.
- Set the Font Name property to Wingdings.
- Select the control's Format property, which will probably display Yes/No.
- The first format component determines how Access displays a false value (0). In the case of a Yes/No field, you want Access to display an empty check box. To enter the appropriate wingding character, hold down the [Alt] key and type 0168 on the numeric keypad — this value represents the empty check box character (for false value).
- Enter a semicolon character (;), which Access requires between formatting components.
- Enter a backslash character (\), which indicates that the character that follows is a literal character.
- To enter the Wingdings value for a checked check box (representing true values), hold down the [Alt] key and type 0254. Don't expect to recognize the characters in the Format property field (see Figure F).
- Save the report and display in Print View.
|The Wingdings characters won't resemble the values you type or the check box control you want displayed.|
When you publish this report, as shown in Figure G, Word has no problem displaying the checked and unchecked check box controls. If the controls are too small, simply adjust the control's Font Size property in Access. (To learn more about formatting values via a control, search on "control formats" in Help.)
|Word displays Wingdings symbols for Yes/No values.|
Wingdings has other check box symbols:
If you use the unboxed check mark, leave the format's false component blank. To see more Wingdings, choose Symbol from the Insert menu and choose Wingdings from the Font control.
Control published controls
What Access and Word lose, you can quickly recoup. If all you need are the values, simply change the Yes/No field's control type. Of course, the graphic check box is a nice touch to a document. To retain the graphic, use Wingdings.
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 firstname.lastname@example.org.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.