One of the more powerful ways to present data in a form or report is to use an option group. Its great advantage is that all possible values for the field are displayed. You specify the value for the active record by selecting its option button. For example, the Ship Via option group shows all three carriers that Northwest Trading uses. The option button for Federal Shipping, the shipper for the active record, has been selected.

It’s best to use option groups for fields with between two and five possible values. An option group of, say, 10 values would be unwieldy and take up a lot of space. Although seemingly straightforward, option groups are actually a bit tricky to create and, especially, to edit. So let’s get straight to work on understanding option groups.

 View The Underlying Tables

Navigate to the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the clipboard and paste it in another folder. Rename the file Copy Of Northwind.mdb, which you can open and use without worrying about changing the original file. If you prefer to work with the original Northwind database, open Access and click Help, Sample Databases, and Northwind Sample Database.

Click the Tables button in the Northwind: Database Window. Double-click the Shippers table to open it. The first field, ShipperID, is the primary key, and the second field is the Company Name. For future reference, note the primary key for each shipper: (1) Speedy Express; (2) United Package; and (3) Federal Shipping. Close the Shippers table.

Open the Orders table. Scroll to the Ship Via field; all of its values are from the Shippers table. Click View. Scan down the list of fields and note that the ShipVia field has a Number data type.

Why does the ShipVia field have a Number data type if it displays text? ShipVia is a Lookup field that "looks to" another table (for instance, Shippers) for its data. The field identifies values by their primary key (that is, ShipperID) and displays the Company Name for that ID. Close the Orders table.

 Create The Form

We’ll create a form with a few of the fields in the Orders table, using an option group for the ShipVia field. In the Database: Window, click Forms. Click the New button and in the New Form dialog box, click Design View to highlight it. Open the Choose The Table Or Query Where The Object’s Data Comes From drop-down menu and click Orders. Click OK and if necessary, maximize the window.

The field list should be in view; if not, choose View and Field List. Click OrderID, press SHIFT, and click Employee ID. You’ll see that CustomerID is also selected. Drag the three fields to the design area and drop them 1inch from the left and two rows of dots from the top.

In the Toolbox, make sure the Control Wizards button is selected; it’ll be highlighted if it is. (If Toolbox isn’t displayed, choose View, Toolbox.) Click the Option Group button in the Toolbox and drag the pointer so the crosshair is 3 inches from the left and two rows of dots from the top. Click to begin the Option Group Wizard.

 Create The Option Group

First we’ll type Label Names for each value, putting them in alphabetical order. Type Federal Shipping and press Tab. Repeat the procedure for Speedy Expressand United Package. Click Next.

In this dialog box, click No, I Don’t Want A Default and then click Next.

In this dialog box, you’ll set a value for each option. You want the values to match the primary key, that is the ShipperID, for each shipper. Type the value 3for Federal Shipping, 1for Speedy Express, and 2 for United Package. Click Next.

Click Store The Value In This Field. Open the drop-down menu and select ShipVia, the field that contains the shipper data. Click Next.

In this dialog box, you’ll choose graphical elements. Experiment with different controls and styles to see various combinations in the sample at left. When you’re through, choose Option Buttons for the control type and Etched for the style. Click Next.

Type Ship Viato label your option group and click Finish. Click View to see the form with the option group. On your own, open the Orders table and test a few records to verify that the data in the table matches that in the form, specifically for the Ship Via field. Click View to return to Design View.

 Edit An Option Group

Structurally an option group is complex, so let’s get a better understanding of its various elements. The frame that surrounds the options is the Group Frame. It’s the only part of the group that is a bound object; it’s bound to the field in the underlying table. You can verify this by right-clicking the right border of the frame, clicking Properties, and then clicking the Data tab. The ControlSource is the ShipVia field.

Click the text Ship Via. As you can see in the property sheet, there is no data source; it’s merely a label. Click Speedy Express. There is no data source here either; the company names are also labels.

Click the Speedy Express option button. There’s no data source, but there is an Option Value. It’s the primary key for that value in the underlying table.

 Edit For Style

Let’s change the style of the option group. Click the option frame and click the Format tab. Click in the Special Effect property, open the list, and choose Sunken. Click View to see the new style.

You can save the form and type the name Smart Computing Option Groupor simply close the form without saving your changes.