Combo-Box Column Values

Introduction

The Text Boxes and Command Buttons are the most frequently used controls on a Microsoft Access form. They serve as the primary means of user interaction — Text Boxes for data input or display, and Command Buttons for triggering specific actions through macros or VBA procedures.

We have explored several techniques involving these controls in earlier articles, including how to enhance their functionality, improve their visual behavior, and automate form operations. For readers who have not encountered those posts before, the links to those articles are provided below for easy reference. Review them to gain a better understanding of how these controls can be customized and utilized effectively in your own applications.

1. Command Button Animation
2. Command Button Animation-2
3. Double-Action Command Button
4. Colorful Command Buttons
5. Transparent Command Button

Next in line is the most preferred and familiar control on Forms: the Combo Box control. This versatile control permits users to select a value from a predefined list or type in a new one. The Combo Box can be created not only on Forms but also directly in Tables, providing an easy and efficient way to maintain data integrity and consistency.

If you would like to see a few examples of how Combo Boxes are used in Tables, you already have them on your PC. Open the Northwind.mdb sample database located at:

C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb
(for Microsoft Office 2003 — the path may vary in later versions)

Now open the following Tables in Design View and observe the Field Properties:

• Employees Table

• Orders Table

• Order Details Table

You will find several fields that use lookup lists, where the Display Control property is set to Combo Box. This helps restrict data entry to valid values while still allowing flexibility through a user-friendly dropdown list.

1. Employees Table

   • Field: Title of Courtesy

   Note: Select the Lookup Tab from the Field Properties below. Inspect the Row Source Type and Row Source Property Values. Inspect the other related Property settings as well.

   The Row Source Property Values are keyed in as Constants separated by semicolons after setting the Row Source Type value as Value List. This is a single-column list.

   To enter values in a two-column list, the Column Count Property value must be 2, and based on that, the values entered into the Row Source property will be read in pairs to display.

   When using the constant values (Dr., Mr., Miss, Mrs., Ms.) as Source items for the ComboBox, it is assumed that values other than these are not likely to enter this field, but if necessary, you may enter them manually into the target field. The Limit To List Property Value setting of No suggests this.

   • Field: ReportsTo

   Note: The Row Source Property value EmployeelD is taken in the ReportsTo Field from the same Employees Table.

   The Row Source Type Value is Table/Query, and the Row Source Value is an SQL statement, which pulls data from selected fields from the Employees table itself.

   Here, the Limit To List property value is set to Yes, indicating that values other than what is appearing in the ComboBox (or in the Employee ID field) will not be accepted in the Combobox Text Box to select or enter. In other words, you cannot enter a value manually into this field other than what is appearing in the combo box.

   The Combo-Boxes created in the Table Structure have more control over the data going into the target field than an external Combo-Box created on a Form. A Form or Query designed with this Table Column will have the Combo-Box attached to it when the Field is dragged from the Field-List to the Form or Query, or when you create a Form or Report with the built-in Wizards.

2. If it is absolutely necessary to add a new value to the ComboBox, then that should be added to the Source Table either manually or through a VBA Program first. After that, refresh the ComboBox contents so that the newly added value appears in the ComboBox's List. 

3. Orders Table
   
   • CustornerlD
   • EmployeelD
   • Ship via

   Open the Orders Qry in normal view and click on one of the rows of the Customer Column to see the Combo-Box in action on the Query.

4. Order Details Table
   • ProductID

The Bound Column Property

Normally, a Combo Box has one or more columns of information — for example, Employee ID and Employee Name. The value that is actually stored in the target field depends on the Bound Column property setting.

If the Bound Column is set to 1, then the first column value (usually the unique key, such as Employee ID) is saved into the field. The second column (for example, Employee Name) is displayed in the Combo Box for clarity and user convenience, but is not stored in the table.

This dual-column approach provides both efficiency and readability — users can easily identify records by name, while the database continues to work with the key values needed for relational integrity.

The Column Width Property must be set with Values like 0.5"; 1.5" for each column, and the List Width Property Value is equal to the value of all Column Widths added together.

Assume that our Employee Combo Box has three columns: EmployeeID, Employee Name, and Designation. When the user selects an employee from the Combo Box, not only should the EmployeeID be stored in the ReportsTo field, but the Designation should also be displayed automatically in another control (for example, a text box named txtDesignation) on the same Form.

This can be done with a single line of VBA code in the AfterUpdate or On Click event procedure of the Combo Box:

Me!txtDesignation = Me!cboEmployee.Column(2)

Here’s what’s happening in this line:

• Me!cboEmployee refers to the Combo Box control on the Form.

• .Column(2) refers to the third column (remember: column numbering starts from 0, so Column(0) = EmployeeID, Column(1) = Employee Name, and Column(2) = Designation).

• The value from that column is assigned to the txtDesignation text box.

This method can be extended to extract and use values from multiple columns of the Combo Box to populate different fields or text boxes in the Form, all with just a few lines of code.

Private Sub EmployeeCombo_Click()
     Me![Designation] = Me!EmployeeCombo.Column(0,2)
 End Sub

The Row/column index numbers of combo boxes are 0-based, and the third column has an index value of 2.

The value 0 points to the First row in the Combo Box Value List, and the value 2 picks the third column value.

Earlier Post Link References:

• Sum() Min() Max() ParamArray
• Text Box and Label Inner Margins
• Multiple Parameters for Query
• Form Menu Bars and Toolbars
• Seriality Control - Missing Numbers