Using Combo Boxes in a Form

From NeoWiki

(Difference between revisions)

Current revision

This article is part of the To Base and Back Again tutorial series.

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.

1 Adding a Combo Box to Forms (except Data Sheets)
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 in 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, and then only if you will not be using the Form Navigator technique. If you are creating a form in design view, skip directly to the second step of Creating a Combo Box by Drawing a New Control.

Open the database document
Click on the Forms icon in the left column
Right-click or control-click on the Locations Data Entry icon in the Forms section and choose Edit from the contextual menu that appears.
Click on Terrain_Type.
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.
Click elsewhere on the form

Creating a Combo Box by Drawing a New Control

This method invokes the Combo/List Box Wizard, which makes configuring the combo box somewhat simpler. It is also the method to use if you are Creating a Form in Design View and need a combo box.

Click on the blank text box under the words Terrain_Type and hit the delete key
Make sure the Form Controls toolbar is visible. If not, under the View menu choose Toolbars and then Form Controls.
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 move your mouse over the icons to find the right one.)
Move your mouse back onto the form. The cursor has turned into cross.
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
In the list of tables, click on Locations.
Click Next>>
In the Existing Fields section, click on Terrain_Type
Click Next>>
Select Yes, I want to save it in the following field:
Select Terrain_Type from the drop-down menu
Click Finish
Double click on the combo box you have just created. A Properties window opens. In the General tab, type a more descriptive name in the Name field. This will make using the form Navigator simpler.
Note that you can specify a Default text for the combo box. We don't need to do this for the Terrain_Type field, but we will be using it below.
Note that you can also change several other formatting options including Font and Alignment.
Close the Properties window by clicking on the little red circle on the top left.

Creating a Combo Box by Using the Replace Command

This method eliminates the need to delete the first form control, but it does not trigger the wizard, so you have to set the properties manually.

Prepare the "StateOrProvince" field as described in Preparing the Form for the Combo Box
Right-click or control-click on the text box under the "StateOrProvince" label. Choose Replace With and then Combo Box from the contextual menu.
Double click the combo box field
In the General tab, give the combo box an identifying name in the Name field.
In the Default Text field, enter the default abbreviation for your state or province, or one of the abbreviations from our "dummy" data.
Make sure that the Dropdown field is set to Yes
Click on the Data tab
Make sure that the Data field is set to StateOrProvince
Set the Type of list contents to Sql
In the List contents field, enter the following SQL statement:
SELECT DISTINCT "StateOrProvince" FROM "Locations"
Close the Properties window by clicking on the little red circle in the upper left hand corner.

Using Replace With in the Form Navigator

Using the Form Navigator means you don't have to ungroup labels and form controls. It is also an easy way to quickly adjust several different form controls. If you plan to use the Form Navigator, it is important to give all your controls meaningful names, so you can quickly identify them in the list on the Navigator.

Make sure the Form Design toolbar is visible. (Under the View menu choose Toolbars then Form Design.)
Click on the Form Navigator button of the Form Design toolbar. It looks like a form icon with a small navigator compass. The Form Navigator window will open.
Right-click or control-click on the Water_Type field in the Navigator list and choose Replace With and then Combo Box from the contextual menu.
Right-click or control-click again on the field and choose Properties... from the contextual menu.
Set the properties as specified in the Creating a Combo Box by Using the Replace Command section; just be sure to adjust the SQL code to reflect the correct field.

Notes

This process (using either method) should to be repeated for the the County and Country fields. Similarly, data entry will be easier on the Field Trips Data Entry form if the Field_Trip_Location field is a combo box based on the Location_Name from the Locations table.
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.

Open the database document
Click on the Forms icon on the left hand column
Right-click or control-click on the Field Trips Data Entry form and choose Edit... from the contextual menu.
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.
Right-click or control-click once again on the Bird Name column header in the sub-form and choose Column...
Click on the Data tab in the Properties: Combo Box window
Set Type of list contents.... to Sql
In the List Content... box, type the following SQL statement:
SELECT DISTINCT "EnglishName" FROM "AOU_Birdlist"
Close the Properties: Combo Box window by clicking on the red dot.
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.

This article in other languages: FranÃ§ais

Retrieved from "https://neowiki.neooffice.org/index.php/Using_Combo_Boxes_in_a_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 add new terrain types directly from the '''Locations Data Entry''' form, rather than needing to open the '''Terrain Types''' table.
+For our example, we will create a combo box for the '''Terrain Type''' field in 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 [[Using Combo Boxes in a Form#Creating the Combo Box]]
+These steps are only necessary for forms created using the Form Wizard, and then only if you will not be using the [[#Using Replace With in the Form Navigator|Form Navigator]] technique.  If you are creating a form in design view, skip directly to the second step of [[Using Combo Boxes in a Form#Creating the Combo Box|Creating a Combo Box by Drawing a New Control]].
 #Open the database document
 #Click on the {{prefName|Forms}} icon in the left column
-#Right-click on the {{prefName|Locations Data Entry}} icon in the {{Section|Forms}} section and choose {{Menu|Edit}} from the contextual menu that appears.
+#Right-click or control-click on the {{prefName|Locations Data Entry}} icon in the {{Section|Forms}} section and choose {{Menu|Edit}} from the contextual menu that appears.
-#Click on '''Terrain Type.'''
+#Click on '''Terrain_Type.'''
-#The Label and the (blank) text box have been grouped in order to proceed, they must be ungrouped:  Under the {{Menu|Format}} Menu, choose {{Menu|Group}} and then {{Menu|Ungroup}}.
+#The Label and the (blank) text box have been grouped. In order to proceed, they must be ungrouped:  Under the {{Menu|Format}} Menu, choose {{Menu|Group}} and then {{Menu|Ungroup}}.
 #Click elsewhere on the form
-*#Click on the blank text box under the words '''Terrain Type'''
+<br>
-#Hit the {{Key|delete}} key
-===Creating the Combo Box===
+===Creating a Combo Box by Drawing a New Control===
+This method invokes the Combo/List Box Wizard, which makes configuring the combo box somewhat simpler.  It is also the method to use if you are [[Creating a Form in Design View]] and need a combo box.
-#Make sure the Form Controls toolbar is visible.  If not, under the {{Menu|View}} choose {{Menu|Toolbars}} and then {{Menu|Form Controls}}.
+#Click on the blank text box under the words '''Terrain_Type''' and hit the {{Key|delete}} key
-#Click on the {{prefName|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.)
+#Make sure the Form Controls toolbar is visible.  If not, under the {{Menu|View}} menu choose {{Menu|Toolbars}} and then {{Menu|Form Controls}}.
+#Click on the {{prefName|Combo box}} icon.  (The icon with a blank drop-down box and a scrolling list below it.  Watch the tooltips as you move your mouse over the icons to find the right one.)
 #Move your mouse back onto the form.  The cursor has turned into cross.
 #Drag to create the combo box in the location where the blank text box was before.
 #:-->A combo box control appears, and the {{Window|Combo Box Wizard}} appears
-#In the list of tables, click on '''Terrain Types'''.
+#In the list of tables, click on '''Locations'''.
 #Click {{Button|Next>>}}
-#In the {{Section|Existing Fields}} section, click on '''Terrain'''
+#In the {{Section|Existing Fields}} section, click on '''Terrain_Type'''
 #Click {{Button|Next>>}}
 #Select {{prefName|Yes, I want to save it in the following field:}}
-#Select {{prefName|TerrainType}}  from the drop-down menu
+#Select {{prefName|Terrain_Type}}  from the drop-down menu
 #Click {{button|Finish}}
+#Double click on the combo box you have just created.  A {{Window|Properties}} window opens.  In the {{Section|General}} tab, type a more descriptive name in the {{prefName|Name}} field.  This will make using the form Navigator simpler.
+#Note that you can specify a {{prefName|Default text}} for the combo box.  We don't need to do this for the Terrain_Type field, but we will be using it below.
+#Note that you can also change several other formatting options including {{prefName|Font}} and {{prefName|Alignment}}.
+#Close the {{Window|Properties}} window by clicking on the little red circle on the top left.
+<br>
-===Notes===
+===Creating a Combo Box by Using the Replace Command===
-* During data entry, if you enter a value not in the combo box, it will be written to the Locations table, but not to the Terrain Type table.  For consistancy's sake, it is best to enter Terrain Types in the Terrain Types table as much as possible.
+This method eliminates the need to delete the first form control, but it does not trigger the wizard, so you have to set the properties manually.
-* This process needs to be repeated for any fields for which there exist "convenience" tables.  (e.g. StateOrProvince, WaterType, etc.)
+#Prepare the "StateOrProvince" field as described in [[#Preparing the Form for the Combo Box|Preparing the Form for the Combo Box]]
-* 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.
+#Right-click or control-click on the text box under the "StateOrProvince" label.  Choose {{Menu|Replace With}} and then {{Menu|Combo Box}} from the contextual menu.
+#Double click the combo box field
+#In the {{Section|General}} tab, give the combo box an identifying name in the {{prefName|Name}} field.
+#In the {{prefName|Default Text}}  field, enter the default abbreviation for your state or province, or one of the abbreviations from our "dummy" data.
+#Make sure that the {{prefName|Dropdown}} field is set to {{prefName|Yes}}
+#Click on the {{Section|Data}} tab
+#Make sure that the {{prefName|Data field}} is set to <tt>StateOrProvince</tt>
+#Set the {{prefName|Type of list contents}} to {{prefName|Sql}}
+#In the {{prefName|List contents}} field, enter the following SQL statement:
+#:{{preBox|SELECT DISTINCT "StateOrProvince" FROM "Locations"}}
+#Close the {{Window|Properties}} window by clicking on the little red circle in the upper left hand corner.
+<br>
+===Using Replace With in the Form Navigator===
+Using the Form Navigator means you don't have to ungroup labels and form controls.  It is also an easy way to quickly adjust several different form controls.  If you plan to use the Form Navigator, it is important to give all your controls meaningful names, so you can quickly identify them in the list on the Navigator.
+#Make sure the Form Design toolbar is visible.  (Under the {{Menu|View}} menu choose {{Menu|Toolbars}} then {{Menu|Form Design}}.)
+#Click on the {{Button|Form Navigator}} button of the Form Design toolbar.  It looks like a form icon with a small navigator compass.  The {{Window|Form Navigator}} window will open.
+#Right-click or control-click on the '''Water_Type''' field in the Navigator list and choose {{Menu|Replace With}} and then {{Menu|Combo Box}} from the contextual menu.
+#Right-click or control-click again on the field and choose {{Menu|Properties...}} from the contextual menu.
+#Set the properties as specified in the [[#Creating a Combo Box by Using the Replace Command|Creating a Combo Box by Using the Replace Command]] section; just be sure to adjust the SQL code to reflect the correct field.
+===Notes===
+* This process (using either method) should to be repeated for the the  '''County''' and '''Country''' fields.  Similarly, data entry will be easier on the '''Field Trips Data Entry''' form if the '''Field_Trip_Location''' field is a combo box based on the '''Location_Name''' from the '''Locations''' table.
+* 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 {{Section|Data}} tab.  You will notice that the {{prefName|Type of list contents}} says {{prefName|SQL}}.  Directly below that, the {{prefName|List Content}} box will have an SQL statement based on this pattern:
+*:{{preBox|SELECT DISTINCT "FieldName" FROM "TableName"}}
+:Simply replace the Field name and Table name as necessary.
-==Using a Combo Box in a Data Sheet Style Form==
+==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.
+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.
 #Open the database document
 #Right-click or control-click on the {{prefName|Field Trips Data Entry}} form and choose {{Menu|Edit...}} from the contextual menu.
 #When the form opens, right-click or control-click on the '''Bird Name''' column header in the sub-form and choose {{Menu|Replace With...}} and then {{Menu|Combo Box}}.
-#Rick-click or control-click once again on the '''Bird Name''' column header in the sub-form anc choose {{Menu|Column...}}
+#Right-click or control-click once again on the '''Bird Name''' column header in the sub-form and choose {{Menu|Column...}}
 #Click on the {{Section|Data}} tab in the {{Window|Properties: Combo Box}} window
 #Set {{prefName|Type of list contents....}} to {{prefName|Sql}}
 #In the {{prefName|List Content...}} box, type the following SQL statement:
-#:{{preBox|SELECT DISTINCT "EnglishName" FROM "AOU Birdlist"}}
+#:{{preBox|SELECT DISTINCT "EnglishName" FROM "AOU_Birdlist"}}
 #Close the {{Window|Properties: Combo Box}} window by clicking on the red dot.
-#Now click the {{Button|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.
+#Now click the {{Button|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.
 {{botlangbarEN|[[Fr:Utiliser une BoÃ®te CombinÃ©e dans un Formulaire|FranÃ§ais]]}}

Using Combo Boxes in a Form

From NeoWiki

Current revision

Contents

Adding a Combo Box to Forms (except Data Sheets)

Preparing the Form for the Combo Box

Creating a Combo Box by Drawing a New Control

Creating a Combo Box by Using the Replace Command

Using Replace With in the Form Navigator

Notes

Creating A Combo Box in a Data Sheet Style Form

Views

Personal tools

Navigation

Search

Toolbox