[Rabbit]

Cascading Combo/List Boxes

This tutorial is to guide you in the creation of Cascading combo/list boxes. That is when you have multiple combo/list boxes where the selection of an option in one determines the available options in the other.

TERMINOLOGY

Row Source: The table/query from which the Combo Box or List Box gets its values. Note: There are other types of row sources that can be used but for simplicity we will stick with Tables and Queries.

Control Source: In a bound form, this determines the field that the control represents. This differs from the Row Source in that the Row Source determines your choices, the Control Source determines where the information is stored.

Column Count: This option let’s you define how many columns of data from the row source you wish to be able to view and access from the control.

Column Widths: Determines the size of the columns in form view. In a multi-column combo/list box, if you wish to keep access to the information but you don’t want to display the column, set the width to zero.

Bound Column: For a multi-column combo/list box, this option designates the column that is stored in the underlying table when a selection is made.

After Update event: This event occurs after changed data in a control or record is updated.

Form Module: A form module contains all code that is specific to the form which it represents.

Bound Form: A bound form is one that is linked to a table/query that will store the information that is entered into the form. Note: This is not always the case but it is the default. An unbound form is one that is not linked and therefore the information entered into the form will no longer be available once the form is closed.

CONCEPTS

Event-Driven Nature of Access: Everything in a form in Access is event driven. Certain events, such as the click of a mouse or the press of a key, can be used to run user defined macros or code outside the normal function of the event. The events available at your disposal are determined by the object, i.e. the form, the text box, the combo box. These can be viewed from the properties of the object.

Accessing the properties of an object: The properties and functions of an object are organized into hierarchies. Using the background color property of a text box named Subtotal on a form called Foo as an example, the property belongs to the control Subtotal, Subtotal belongs to form Foo, and Foo belongs to the collection Forms. To reference the property, you would use Forms.Foo.Subtotal.BackColor. Within a form module there are certain assumptions made when you do not refer to an object by its full object path. However, for clarity, we will not get into these assumptions. But, we will be using the Me reference. Keyword Me references the current form that evoked the event. So you can use Me.Subtotal.BackColor.

ASSUMPTIONS
We will use a simple scenario for this tutorial. You have an unbound form with two combo boxes. One named [Company] and the other named [Employee Name]. [Company] will get its values from table TblCompany while [Employee Name] will get its values from TblEmployees. The tables have the following layout:

Expand|Select|Wrap|Line Numbers

1. TblCompany
2. [ID] – AutoNumber, PK
3. [CpyName] – Text, Name of the Company
4.  
5. TblEmployees
6. [EmpName] – Text, Name of Employee
7. [ID] – FK, Used to link the employee to the company from which they work.
8. [EID] – Autonumber, PK

As a default, [Company] will have the following properties:
Row Source – TblCompany
Column Count – 2 (We use 2 columns because we want to include both ID and CpyName.)
Column Widths – 0”;1” (We set the first column to 0” because the user does not need to see the ID.)
Bound Column – 1 (We bind it to the first column so that when we refer to [Company], it will return the ID rather than CpyName.)

And [Employee Name] will have no options because we want it to be empty until a company has been chosen.

Your needs will determine how you will set up your combo/list boxes and tables.

PROCEDURE
What we want is to change the Row Source of [Employee Name] whenever the user makes a change of selection to [Company].

So, in the After Update event property of [Company], you’ll want to change it to [Event Procedure].

Then, in the Visual Basic Editor, in the Module for form Foo, you’ll have the following code:

Expand|Select|Wrap|Line Numbers

1. Private Sub Company_AfterUpdate()
2.   With Me![Employee Name]
3.     If IsNull(Me!Company) Then
4.       .RowSource = ""
5.     Else
6.       .RowSource = "SELECT [EmpName] " & _
7.                    "FROM TblEmployees " &  _
8.                    "WHERE [ID]=" & Me!Company
9.     End If
10.     Call .Requery
11.   End With
12. End Sub

So, assuming the user chooses CompanyXYZ that has an ID of 6, the user will see CompanyXYZ in [Company] and the list for [Employee Name] will populate with values where the TblEmployee record has an ID value of 6.

[NeoPa]

A related article can be found at Example Filtering on a Form.