Using Combo Boxes in a Form

From NeoWiki

Forms can include Combo Boxes and/or List Boxes to make data entry easier. List Boxes force the user to choose one of a list of pre-defined values. New values cannot be added. List Boxes use references. Combo Boxes do not use references, and allow users to choose from a pre-defined list or to add a new value. For more information on the differences between these two, type Combo Box/List Box Wizard into the Search Term field in the NeoOffice Help window.

Contents 1 Adding a Combo Box to Forms (except Data Sheets) 1.1 Preparing the Form for the Combo Box 1.2 Creating the Combo Box 1.3 Notes 2 Creating A Combo Box in a Data Sheet Style Form

Adding a Combo Box to Forms (except Data Sheets)

For our example, we will create a combo box for the Terrain Type field the the Locations Data Entry form. (see Creating a Simple Data Entry Form) We want to use a combo box here so that we can keep the entry of terrain types standardized while allowing for the entry of new terrain types.

Preparing the Form for the Combo Box

These steps are only necessary for forms created using the Form Wizard. If you are creating a form in design view, skip directly to Creating the Combo Box

1. Open the database document
2. Click on the Forms icon in the left column
3. Right-click on the Locations Data Entry icon in the Forms section and choose Edit from the contextual menu that appears.
4. Click on Terrain Type.
5. The Label and the (blank) text box have been grouped in order to proceed, they must be ungrouped: Under the Format Menu, choose Group and then Ungroup.
6. Click elsewhere on the form
7. Click on the blank text box under the words Terrain Type
8. Hit the delete key

Creating the Combo Box

1. Make sure the Form Controls toolbar is visible. If not, under the View choose Toolbars and then Form Controls.
2. Click on the Combo box icon. (The icon with a blank drop-down box and a scrolling list below it. Watch the tooltips as you mouse over the icons to find the right one.)
3. Move your mouse back onto the form. The cursor has turned into cross.
4. Drag to create the combo box in the location where the blank text box was before.

   -->A combo box control appears, and the Combo Box Wizard appears

5. In the list of tables, click on Locations.
6. Click Next>>
7. In the Existing Fields section, click on TerrainType
8. Click Next>>
9. Select Yes, I want to save it in the following field:
10. Select TerrainType from the drop-down menu
11. Click Finish
12. Double click on the combo box you have just created. In the General section, type a more descriptive name in the Name field. This will make using the form Navigator simpler.

Notes

• This process should to be repeated for the the WaterType, StateOrProvince, County, and Country fields. Similarly, data entry will be easier on the Field Trips Data Entry form if the Location field is a combo box based on the LocationName from the Locations table.
• Some individuals may find it easier to create the form using Design View, rather than the Form Wizard, since adding combo boxes requires ungrouping and deleting fields if the Form Wizard has been used.
• To edit the table and/or fields from which the combo box list is derived, double click on the combo box (in Design View), and click the Data tab. You will notice that the Type of list contents says SQL. Directly below that, the List Content box will have an SQL statement based on this pattern:

  SELECT DISTINCT "FieldName" FROM "TableName"

Simply replace the Field name and Table name as necessary.

Creating A Combo Box in a Data Sheet Style Form

Setting up a Combo Box is more complicated when the form uses a Data Sheet (spreadsheet-like) format. We need to use this technique in our Field Trips Data Entry form. (See Creating A Data Entry Form With A Subform).) Entering bird names correctly in the subform will be much simpler if we use a combo box.

1. Open the database document
2. Click on the Forms icon on the left hand column
3. Right-click or control-click on the Field Trips Data Entry form and choose Edit... from the contextual menu.
4. When the form opens, right-click or control-click on the Bird Name column header in the sub-form and choose Replace With... and then Combo Box.
5. Rick-click or control-click once again on the Bird Name column header in the sub-form anc choose Column...
6. Click on the Data tab in the Properties: Combo Box window
7. Set Type of list contents.... to Sql
8. In the List Content... box, type the following SQL statement:

   SELECT DISTINCT "EnglishName" FROM "AOU_Birdlist"

9. Close the Properties: Combo Box window by clicking on the red dot.
10. Now click the Design Mode On/Off button (the one that looks like a blue drafting triangle) to shift to data entry mode. Enter a test set of data. Note that when you begin typing in a cell in the BirdName column, bird names appear. You can also click on the triangle to see a list of bird names.

Note that you will not be able to enter a bird name that does not exist in the EnglishName field of the AOU_Birdlist table because of the existing relationship between the AOU_Birdlist and Sightings table.