Combo-Box Column Values

Introduction

The Text Boxes and Command Buttons are the most commonly used controls on a Form. We have seen them in action in different ways and with different designs. For those who have not come across those Articles before then links to them are given below; you may take a look at them.

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 can be created not only on Forms but also on Tables as well.

If you would like to see a few examples for the usage of Combo-Boxes in Tables then you have them on your PC itself. Open the Northwind.mdb, sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MS-Office2003 pathname) and open Employees Table or Orders Table or Order Details Table in Design View and look at the Fields and Properties given under each Table listed below:

1. Employees Table
   • Field: TitleofCourtesy

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

   The Row Source Property Values are keyed-in as Constants separated by semi-colons 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.

   While using the constant values (Dr.; Mr.; Miss; Mrs.; Ms.) as Source items for the Combo-Box it is assumed that values other than these are not likely to enter into 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 Combo-Box (or in the EmployeelD field) will not be accepted in this field. In other words, you cannot enter a value manually into this field, other than what is appearing in the Combo-Box.

   With this setting, an automatic validation check is performed by the Combo-Box to prevent invalid values from creeping into the target data field. If the new value is required, then that must be entered into the source Table/Field of the Combo-box first before that can be inserted into the target field.

   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. Orders Table
   
   • CustornerlD
   • EmployeelD
   • Ship via

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

3. Order Details Table
   • ProductID

The Bound Column Property

Normally, Combo-Box will have one or more columns of information like EmployeelD and Employee Name. When clicked the first column value is inserted (this depends on the Bound Column Property Value setting) into the target field and the description appearing in the second Column is shown for information purposes only.

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.

In our above example, the EmployeelD is the value inserted into the ReportsTo field when clicked. The EmployeelD is a number, but the descriptive name is important to the User because it is more meaningful and easy to remember. So in the Combo-Box, the EmployeelD number is kept hidden by setting the first Column Width Value as 0", thereby showing only the name of the Employee in the Combo-Box.

We will go back to the first sentence of the above Paragraph and proceed further on that point. There are times that we need information from other Columns also to insert into other target fields with one click.

Assume that our Employee Combo-Box has one more column for Designation (besides EmployeelD and Name) and when clicked this information also must be inserted into another control on the Form. You can do this with a one-line VBA Code in the On Click Event Procedure in the Form Module like:

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