databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Forms and Combo Boxes

Combo Boxes in Microsoft Access:

MS Access is a very versatile data handling software. The Microsoft Access Combo box is one of the many features provided which generate elegant user data entry interfaces. A combo box is a data capture control just like a text box. It has a drop down list of predefined values. The user can click on any of these values to populate the text box. Alternatively, data can be typed into a combo box too.

By using a combo box the user does not need to remember the exact data to be entered. Moreover, it standardizes the values entered reducing data entry errors. In case of multi field combo boxes, many fields can be displayed, giving the user greater information.

Combo Box Example

Consider this combo box. The drop down list displays the months of the year. Now the user will choose from this list only. This will prevent the variation in the entry of key fields that causes great confusion while retrieving data.

The main properties of a combo box are:

Property Type Description
Control source The combo box can be bound to any field of the source table attached to the form or remain unbound. The control source property contains the field name or expression to be used as the source for the bound control. For an unbound control, this field is empty.
Row Source type and Row Source The value for Row Source property is dependent on the Row Source type property. There can be three types of inputs for Row Source type property.
  1. Table/query: In this option the combo box is populated by a table or query. The related value for the Row Source property is the name of the table or query. A Query Builder can be used to generate a new SQL Select Statement as the Row Source.
  2. Field list: In this option the combo box is populated by the list of fields of the specified table or query. In the Row Source property the name of the table/query is to be specified.
  3. Value list: In this option the values displayed will have to be specified or hard coded. Each individual option of the list of values will have to be typed in separated by a semi colon in the Row Source property.
Column count This indicates the number of columns displayed in the combo box.
Column heads This Yes/No field decides whether the columns displayed will have headings or not.
Column width Give the width of each column in inches, separated by a semi colon. If any column is to be included in the query but hidden from view, enter zero as column width.
Bound column This contains the name of the control with which the combo box is bound.
List rows This indicates the number of rows displayed in the drop down list at a time.
List width This indicates the width of the drop down portion of the combo box. If it is set to auto, the width is adjusted automatically.
Limit to list This is a Yes/No field. When set to Yes the user cannot enter any value other than those in the drop down list. A No setting is used to allow the user to enter values other than the list.
Auto expand This property expands text if leading characters match one of the listed options.
Validation rule Enter the relevant expression to validate the data entered in this control. If no validation is required, leave it blank.
Validation text The text explanation accompanying a validation rule.

These are the major properties of a combo box. All the usual control Event properties (onClick, onDblClick etc.) hold good for the combo box too.

Examples

Here are a few examples to illustrate the usage of a Combo box.

Example 1

For the drop down list displayed above, the Combo box will have the following properties:

Property Attribute
Row Source type Value List.
Row Source 01;"JAN";02;"FEB";03;"MAR";04;"APR";05;"MAY";06;"JUN"; 07;"JULY";08;"AUG";09;"SEP";10;"OCT";11;"NOV";12;"DEC"
Column Count 2
Column Width 0;2"
Bound Column 1
Limit to List Yes

This is an example of a value list.

Example 2

Consider the following table - tblDepartment

tblDepartment
DEPT ID DEPT NAME Group
1 PHYSICS SCIENCE
2 CHEMISTRY SCIENCE
3 MATHS SCIENCE
4 ENGLISH ARTS
5 COMPUTERS SCIENCE
6 BIOLOGY SCIENCE

You want to display a combo box to select dept id for science group only. The following properties will be set for the combo box control.

Property Attribute
Row Source type Table/query
Row Source Select [dept id], [dept name] from tblDEPARTMENT where group = ‘SCIENCE’;
Column Count 2
Column Width 0;2"
Bound Column 1
Limit to List Yes

Example 3

A combo box displaying the name of fields in the DEPARTMENT table is to be displayed. Set the properties of the combo box as follows:

Property Attribute
Row Source type Field List
Row Source tblDEPARTMENT
Column Count 1
Bound Column 1
Limit to List Yes

The drop down box will have the names of all the fields of the table.

Check out some of our other articles that deal with Microsoft Access Combo Boxes: