Home

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 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:

    The Combo Box Wizard

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

    The Combo Box Wizard

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

    Option Group Wizard

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

    Option Group Wizard

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

    Option Group Wizard

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

    Option Group Wizard

  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

    A Combo Box a 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 Design View (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:

    Combo Box in a Form

  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:

    The Lookup Wizard

  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

    The Lookup Wizard

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

    The Lookup Wizard

  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:

    Altair Realtors - Properties

  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 List Box
  12. Click the form

    List Box Wizard

  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
     
    List Box Wizard
  15. Click Next
  16. On the third page of the wizard, in the Available Fields list, click the select all button Select All

    List Box Wizard

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

    List Box Wizard

  19. Click Next

    List Box Wizard

  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

    Ceil Inn - Rooms

  24. Preview the form

    Ceil Inn - Rooms

  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

    Ceil Inn - Rooms

  2. Switch the form to Form View

    Ceil Inn - Rooms

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

Previous Copyright © 2002-2016, FunctionX, Inc. Next