Combo and List Boxes

Bound Combo Boxes

Introduction

A combo box is a Windows control that holds a list of text items. The user can select one item from the list. Microsoft Access supports bound and unbound combo boxes. The application also provides the tools to help you create and configure a combo box using either the Lookup Wizard or the Combo Box Wizard.

A Bound Combo Box

A combo box is said to be bound if its values come from a field of a table. Such a field must be the foreign key that represents the records of a related table.

The easiest way to create a bound combo box is to use a wizard because you would let Microsoft Access configure everything behind-the-scenes after you have just made some selections in the wizard. The combo box can be created in a table where it is referred to as a lookup field, or in a form where it is a formal combo box. If a lookup field is created in a table, it can be added to a form or report where it would be created as a combo box. If only a value was provided for the foreign key of a table, then you must formally create a combo box for it.

Practical Learning: Introducing Combo Boxes

1. Start Microsoft Access
2. In the list of files, click Ceil Inn1
3. In the Navigation Pane, double-click the Occupancies form
4. After viewing the form, in the Views section of the Ribbon, click the button under View and click Design View
5. In the Tools section of the Ribbon, click Add Existing Fields
6. In the Field list, drag EmployeeNumber and drop it on the form.
   Notice that the control is a text box
7. Press Delete to remove the new text box
8. In the Controls section of the Ribbon, click the Combo Box and click in the Detail section of the form
9.  In the first page of the wizard, make sure the first radio button is selected. Read it and click Next:

   

10. In the second page of the wizard, click Table: Employees and click Next

    

11. In the third page of the wizard, double-click FirstName

    

12. Click Next
13. Accept the step and click Next

    

14. Click the arrow of the combo box and select EmployeeNumber

    

15. Click Next
16. In the text box, type Processed By:

    

17. Click Finish
18. On the Ribbon, click Design if necessary.
    In in the Tools section, click Add Existing Fields
19. In the Field List, drag DateOccupied and drop it on the form

    

20. Close the form
21. When asked whether you want to save, click Yes
22. In the Navigation Pane, double-click the Occupancies table to open it
23. Click a cell under Processed For and notice that it is a simple text box
24. In the Views section of the Ribbon, click the View button (or click under it and click Design View)
25. Change the data type of AccountNumber to Lookup Wizard
26. In the first page of the wizard, make sure the first radio button is selected. Read it and click Next
27. In the second page of the wizard, make sure Table: Customers is selected (otherwise, select it) and click Next
28. In the third page of the wizard, double-click FirstName and click Next
29. Accept the step and click Next
30. Accept the label as AccountNumber and click Finish
31. When asked to save the table, click Yes
32. When a message box warns you that some data may be lost, click Yes
33. Close the table
34. In the Navigation Pane, right-click the Occupancies form and click Design View
35. In the Tools section of the Ribbon, click Add Existing Fields
36. In the Field list, drag AccountNumber and drop it on the form,
    Notice that the control is a combo box
37. Preview the form and save it

The Characteristics of a Lookup Field/Combo Box

In some cases, if you create your lookup field of combo box using the wizard, some factors may not produce the expected result. Fortunately, you can modify the configuration of the control. Many options are available.

The configurations of the lookup field can be found in the Lookup section of the bottom part of the table in Design View. The configuration of a combo box of a form can be found in the Data or the All tab of the Property Sheet.

The available characteristics or properties are:

• Display Control: This is only available in a table, not on a form or report. This property allows you to specify how the field would display its value. The default is the combo box, which is suitable for most scenarios
• Row Source Type: This property specifies the type of list that contains the actual values. The default is Table/Query, which indicates that the values are stored in a table or a query. One of the options is Field List, which is mostly available if the list is programmatically created
• Row Source: This is the list of values. There are many techniques you can use to create that list. To build the list, you can right-click the field and click Build. If you know the expression to apply, right-click the field and click Zoom
• Bound Column: This is a number that specifies the number of columns that will connect with the primary key. The default value is 1. This should be the number of columns used in the primary key
• Column Count: This is a natural number that specifies the number of columns that will appear when the user clicks the arrow of the combo box on the field. If you select only one column from the Available Fields list of the second page of the wizard, this property would have a value of 1. Otherwise, if you want more columns, set this property accordingly
• Column Heads: This property specifies whether the top section of the list would have a caption
• Field Widths or Column Widths: The Field Widths appears in a form. The Column Widths appears in a form or a report.
  This property is made of one or more sections separated by semi-colons. Each section contains a number that specifies the width of the corresponding column of the lookup field when the list displays. In most cases, the first column, and that represents the foreign key, is not presented to the user and therefore should be set to 0. Each one of the other sections shows the desired width of the column and the columns can have different widths
• List Rows: This characteristic specifies the number of records that will be visible when the list appears. You should use a value between 4 and 16 (the default). A value higher than 16 is usually too long. Most programming environments (in fact as set in the Microsoft Windows operating system) use 8
• List Width: This value is the total width of the list when it comes up. This should be the sum of numbers from the Field Widths property

Practical Learning: Configuring a Combo Box

1. In the Navigation Pane, right-click the Occupancies form and click Design View
2. In the top section of the form, double-click the EmployeeNumber combo box
3. In the Property Sheet, click the All tab
4. Click Name and type EmployeeNumber and make sure Column Count is set to 2
5. Click Column Width and type 0; 1.65
   Make sure Column Heads is set to No and list Rows is set to 16
6. Click List Width and type 1.65
7. Right-click Row Source and click Build...
8. In the bottom section of the window, replace LastName with FirstName & " " & LastName
9. Close the window
10. When asked whether you want to save, click Yes
11. Close the form
12. When asked whether you want to save, click Yes
13. In the Navigation Pane, right-click the Occupancies table and click Design View
14. In the top section of the table, click AccountNumber
15. In the bottom section of the window, click the Lookup tab
16. Right-click Row Source and click Build...
17. In the bottom section of the window, replace FirstName with FirstName & " " & LastName
18. Close the window
19. When asked whether you want to save, click Yes
20. Click Column Width and type 0; 1.35
21. Click List Width and Type 1.35
22. Switch the table to Datasheet View
23. When asked whether you want to save, click Yes
24. Close the table
25. In the Navigation Pane, right-click the Occupancies form and click Design View
26. On the form, click the AccountNumber combo box and press Delete
27. In the Tools section of the Ribbon, click Add Existing Fields
28. From the Field list, drag each field that is not on the form and drop it on the form
29. Complete the design of the form. Here is an example:

    

30. Switch the form to Form View
31. Close the form
32. When asked whether you want to save, click Yes

An Unbound Lookup Field/Combo Box

Introduction

A combo box (or a lookup field) is said to be unbound if it holds its own list of values instead of the values comming from a column of a table. Microsoft Access provides the means of creating the controls and adding the values. As mentioned for the bound combo box (or a lookup field), you can manually create and configure the control or you can use a wizard.

To easily add a lookup field to a table, use the Lookup Wizard. To easily add a combo box to form, use the Combo Box Wizard. In both cases, in the first page of the wizrd, click the second radio button. If you had created a lookup field in a table, you can drag its item from the Field List to a form or report, in which case it would become a combo box, Of course, the control would receive the same name as the table's column. If you create a combo box on form using the Combo Box Wizard, the control would receive a name that starts with Combo. An example would be Combo21.

Practical Learning: Introducing Unbound Lookup Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click Altair Realtors1 from the previous lesson
3. In the Navigation Pane, right-click the Properties table and click Design View
4. Under Field Name, right-click FinishedBasement and click Insert Rows
5. Type PropertyType and press Tab
6. Change the data type of Property Type to Lookup Wizard
7. In the first page of the wizard, read the second radio button and click it:

   

8. Click Next
9. In the second page of the wizard, under Col1, type Townhouse and press the down arrow key
10. Type Single Family and press the down arrow key
11. Type Condominium

    

12. Click Next
13. Accept the label as PropertyType and click Finish

    

14. In the bottom section of the window, click Caption and type Property Type

The Characteristics of a Lookup Field/Combo Box

As mentioned previously, after creating a lookup field or a combo box using the wizard, you can customize it. In fact, you cab configure the field or the combo box by setting its properties. The configurations of a lookup field are done in the Lookup section of a table in Design View. The configuration of a combo box of a form are done in the Property Sheet. The properties are:

• Display Control: This is the same as mentioned previously
• Row Source Type: For an unbound lookup field or combo box, this property should be set to Value List
• Row Source: This is the list of values. The list is made of sections separated by semi-colons. Each section contains a value (you can optionally set the value in double-quotes)
• Field Widths or Column Widths: This is the total widths of the individual columns

Practical Learning: Controlling an Unbound Lookup Field

1. Under Field Name, right-click FinishedBasement and click Insert Rows
2. Type Condition and press F6
3. In the bottom section of the window, click Lookup
4. Change the following characteristics:
   Display Control: Combo Box
   Row Source Type: Value List
   Row Source Type: Excellent;Good Shape;Needs Repair;Unknown
   
5. To switch the table, in the Design tab of the Ribbon, in the Views section, click the View button
6. When asked whether you want to save, click Yes
7. Update the following records:
    

   Property #	Property Type	Condition
   524880	Single Family	Good Shape
   688364	Single Family	Excellent
   611464	Single Family	Good Shape
   749562	Townhouse	Good Shape
   427115	Single Family
   200417	Condominium	Excellent
   927474	Townhouse	Needs Repair
   682630	Single Family	Good Shape
   288540	Condominium	Good Shape

8. Close the table
9. In the Navigation Pane, right-click the Properties form and click Design View
10. On the Ribbon, in the Tools section of the Design tab, click Add Existing Fields
11. From the Field List, drag PropertyType and Condition, and drop them on the form
12. Complete the design of the form as you see fit. Here is an example:

    

13. Close the form
14. When asked whether you want to save, click Yes

Combo Box Events

When using a combo box, depending on the characteristics of the control, if the user types a value that is not in the list, the control would fire an event named On Not List. You can use this event to display a message to the user and to take an appropriate action.

List Boxes

Introduction

A list box presents a list of items to the user. The items in the list appear each on its own line. The user makes a selection by clicking in the list. Once an item is clicked, it becomes highlighted indicating that it is the current choice. As done with  combo box, you can manually create a list box or use a wizard.

To help you create a list box, Microsoft Access provides the List Box Wizard. This allows you to select the source of data, which could be a table. Then you can select the column that would be displayed as the value of the list box, exactly as done for the combo box.

Practical Learning: Introducing a List Box

1. On the Ribbon, click File and click Open
2. In the list of files, click Ceil Inn2
3. On the Ribbon, click Create
4. In the Forms section, click Form Design
5. In the Property Sheet, change the following characteristics:
   Caption: Ceil Inn - Rooms
   Auto Center: Yes
   Record Selectors: No
   Navigation Buttons: No
6. Right-click the form and click Form Header/Footer
7. Set the background color of the form header section and the form footer section as Accent 1, Darker 50%
8. Add a label to the form header section and set its characteristics as follows:
   Caption: Ceil Inn - Rooms
   Font Name: Bodoni MT Black (if you don't have that font, use Garamond
   Font Size: 26
   Fore Color: #EFF2F7
9. Set the background color of the Detail bar to #EFF2F7
10. Save the form as Rooms
11. To create a list box, in the Controls section of the Ribbon, click the List Box button
12. Click the form

    

13. On the first page of the List Box Wizard, make sure the first radio button is selected and click Next
14. On the second page of the wizard, click Table: Rooms
     
    
15. Click Next
16. On the third page of the wizard, in the Available Fields list, click the select all button

    

17. Click Next 
18. On the fourth page of the wizard, uncheck Hide Key Column

    

19. Click Next

    

20. On the fifth page of the wizard, acccept the dialog box and click Next
21. On the sixth page of the wizard, acccept the dialog box and click Finish
22. Delete the label on the form
23. Position the list box in the top-left section

    

24. Preview the form

    

25. Return the form to Design View and click the list box

The Characteristics of a List Box

The list box and the combo box have a lot of features in common. In fact, their configuration is done the same way. The characteristics or properties of a list box are:

• Column Count: This is the number of columns the list box should display
• Columns Widths: This property holds a combination of the width for each column. The values are separated by semi-colons
• Column Heads: This property specifies whether the top section of the list box will display the columns headers

Practical Learning: Configuring a List Box

1. In the Property Sheet, change the following characteristics:
   Column Widths: 0.5;1;1;1;1
   Column Heads: Yes

   

2. Switch the form to Form View

   

3. Close the form
4. When asked whether you want to save, click Yes
5. Close Microsoft Access