UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Combo Box    
Combo Box

Related Content:
    Cascading Combo Boxes


A Combo Box is a Form Control which has a list of items for a user to choose from. The list is displayed by clicking a drop-down arrow on the Combo Box.

Combo Boxes have multiple uses:

  1. Lookup a Record: Let your user choose which record they want to 'jump-to' on the form by selecting it from a Combo Box.
  2. Data Entry: Combo Boxes expedite the Data Entry process by giving users a limited selection of options to choose from.

Contents

Basic Lookup Combo Box Example

Here is a sample of the final output that would be created by following the example below:

Image:Combo_show_name_store_ID.png

In most circumstances, Combo Box's RowSource is bound to a lookup table and used on a form bound to a related table depending on the lookup table. It is customary to use an autonumber ID instead of saving actual values. For instance, if you have a People table, define a PersonID AutoNumber field that will be the primary key (PK), along with other fields like LastName and FirstName. Then, in other tables, when you want to identify a person, you can use the PersonID key field. For the rationale of using an key instead of actual values, review normalization article.

Prerequisite Setup

In order to use a Combo Box on a form bound to a lookup table, it is necessary to set up a lookup table and a related table.

Defining a lookup table

Using people as example for data to be looked up, a new table would be created, named "tbl_People". The field definition would be thus:
tbl_People
Field Name Field data type Description
PersonID AutoNumber Surrogate primary key
FirstName Text
LastName Text
BirthDate Date/Time
Additional fields ...

Relating the lookup data in other tables

For a lookup table to be useful, there needs to be other tables that depend on the lookup tables. Using people table, we might define an Order table so we can then choose a person to associate a new order with. The Order table structure would then look like this:
tbl_Orders
Field Name Field data type Description
OrderID AutoNumber Surrogate primary key
PersonID Long Integer Foreign key to tblPerson
Additional fields ...
It is strongly recommended that a relationship be defined between tblPersons and tblOrders and Referential Integrity enforced. This aids in preventing entry of invalid data and thus protect your database's integrity.

Essential Combo Box Properties

In form's Design View, the Control Wizard may be enabled to help facilitate configurations of the Combo Box's properties or it may be done by hand. Using the same example tables and assuming the form being designed for is bound to the Orders table, the following properties will be set in the Properties Windows, which can be opened with Alt-Enter. The below table represent the minimum changes required to Combo Box's properties for the Combo Box to function correctly.

Essential Combo Box Properties
Name Value Rationale
Format Tab
ColumnCount 3 This will display three columns. A value less than total numbers selected in RowSource means those columns won't be displayed or be accessible in equations or code.
ColumnWidth 0;2 in;1 in Setting the width to zero for first column effectively hides the column. The second column will be 2 inches. The third column will be 1 inch. The corresponding ListWidth property would be set to 0+2+1, or 3 in. If you want to allow for a scrollbar, add 0.2" to make it 3.2". If your local setting specify centimeters or another unit of measurement, you can still enter "in" after you type a number and Access will convert the value(s) to cm when you exit the control.

If the ListWidth is greater than the sum of the ColumnWidths and the last column width is not specified, it will take the remainder of the space that is allotted.
Data Tab
ControlSource PersonID As expected for proper normalization, we should be inserting ID into the Orders table.
RowSourceType Table/Query This indicates we want to use either table or query. We also can choose from value lists or field lists. See the Value List or Field List for more information.
RowSource
      SELECT 
         PersonID, 
         LastName & ", " & Firstname AS Fullname, 
         BirthDate
      FROM tbl_People
      ORDER BY LastName, Firstname;
This defines what columns we want to display, how we should order it and thus affect how it is presented within the list itself. If the RowSourceType is Table/Query, the RowSource can be a table name, a query name, or an SQL statement.
BoundColumn 1 Even though the columns collection is zero-based and the first column (PersonID) has an index value of 0 when you reference it, the bound column starts with 1, which is the default. The BoundColumn designates the column of data will be read from and inserted to for storing in a table, and what is returned by the Value property. It is less confusing to always keep the bound column at 1 and make the column width of the first column = 0 if you do not want it to show.

The settings given in the above table will hide the ID column from user's view and the user will only see the values while the Combo Box will insert the ID in the dependent table (e.g. Orders table) upon record's saving. There are many other properties that may be desirable to modify and thus further customize the Combo Box's behavior and appearance. Again, the above table represents the minimum changes required to function with a lookup table.

Additional Properties

Those properties listed are very commonly modified to further enhance a Combo Box's functionality. For a complete listing of properties, it is recommended that either help files or MSDN documentation be consulted. MSDN has a complete listing of Combo Box Object members.

Additional Combo Box properties
Name Expected Value Rationale
Format Tab
ColumnHeads Yes/No Toggle whether headers will be displayed or not.
ListRows Integer Specifies how many rows will be displayed in the dropdown. Default is 8 or 16, depending on which version of Access being used. It may be desirable to increase for larger lists.
ListWidth Double How wide the dropdown list will be. Sum the ColumnWidths and add 0.2 to provide room for scrollbar.
Width Double How wide the Combo Box itself will be. Note that Combo Box's width and the dropdown list width can be set independently of each other. It is common to make the width of the combo = the width of the column that is displayed first + 0.2" for the drop-down arrow.
Data Tab
Event Tab
OnMouseMove or OnMouseUp [Event Procedure] Use VBA code to cause the automatic dropdown when the mouse moves over the control, saving the user a click (OnMouseMove) -- or when the user releases the mouse inside the control (OnMouseUp). The code usually inserted is
me.ActiveControl.Dropdown
Other Tab
Name PersonID or cboPersonID The Name of a control is how you will refer to a control in an equation or in code. The Name is NOT what shows in the control when you are looking at the design view. You will see the ControlSource or, if the control is a subform/subreport, you will see the name of the SourceObject.

There are 2 common conventions for naming:

  • make the Name the same as the name of the field in the ControlSource if the control is bound. This eases confusion and is helpful if you change the control type because the name stays the same. Most of the time you will be referencing the Value of a control, which does not need to be specified because it is the default property and the control type does not matter.

  • preface the Name with a prefix that specifies the type of control such as cbo for Combo Box, and then the name of the field specified in the ControlSource. This enables the developer to explicitly refer to the field or the control and shows controls of the same type next to each other in a sorted list

Both camps of folks are adamant about "their" way to do things and will insist their way is the best way. In the end, it doesn't really matter -- pick a convention and stick with it. The main thing is to ensure that the control has a logical name as opposed to a generated name like Text123, Combo456, or Command78.

StatusBarText Text It is usually good practice to put down a brief description of the Combo Box's function. This will then be displayed to the user on the Status Bar, on bottom left of screen. If the field description property is filled in the table design, the StatusBarText will be automatically populated.


Control Source vs Row Source

This diagram illusrates how the Control Source properties, the indicated sources and the Combo Box work together:

image:ComboDemo.png


How to display the value from the other columns of the ComboBox

When using a multi-column ComboBox, it is sometimes desired to also display the value from the other columns after the user has made a selection. For this purpose, we can use the ComboBox.Column property to refer to a specific column, or column and row combination, to retrieve its content.

Syntax

ComboBoxName.Column(col, row)

Where:
col is the column position from the left minus 1 (ComboBox column is zero-based)
row is an Optional argument for the item's row number minus 1

Example Usage

Using the ComboBox example above where the first column for the "PersonID" is hidden, the second column shows the "Fullname", and the third column shows the "BirthDate," we can display the BirthDate of the person selected from the ComboBox in a separate Textbox using the following expression:

CODE

=ComboBoxName.Column(2)

Remember that the BirthDate column is the third column to the right, so we use the index number (2).


Basic Field Value Combo Box Example

This Section could use expansion. Please help us by adding to this section if you have knowledge of the topic.

This section is a placeholder. Please help us exand it.

Edit Discussion
Thank you for your support!
This page has been accessed 26,289 times.  This page was last modified 19:59, 7 November 2012 by TheDBguy. Contributions by Jack Leach, Glenn Lloyd, Walter Niesz and strive4peace2010 and others  Disclaimers