472,328 Members | 2,119 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,328 developers and data experts.

Cascading Combo/List Boxes

12,516 Expert Mod 8TB
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.


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.


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.

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
  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.

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.
Feb 21 '07 #1
4 63725
32,511 Expert Mod 16PB
A related article can be found at Example Filtering on a Form.
May 22 '08 #2
Hey Just wanted to say thanks for the help. l finally got the form to do what l wanted it to do. Thanks again.

Apr 5 '11 #3
220 128KB
Amazing. That is some nice compact work. Would you please post a new article on how to do this in C++11? Or, maybe some other expert that you know that is good at C++11 might do that?

Thank you.
Oct 15 '20 #4
12,516 Expert Mod 8TB
Unfortunately, I don't work in c++
Oct 16 '20 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

by: RZ15 | last post by:
Hi, I've been through the tutorial for this and I've tried applying it to my database and it almost works but not quite. i have 2 combo boxes,...
by: pouj | last post by:
Hey i need help with setting up this on ACCESS 2007 i need step by step i need a form... i select a name (drop list)...then gives me more choice...
by: kcdoell | last post by:
Good Morning: I have a form where I am trying to create cascading combo boxes. I have done this before but I am getting the following error that...
by: kcdoell | last post by:
I have 5 cascading combo boxes on a form. Below is a sample of my vb in the first combo box: Private Sub CboDivision_AfterUpdate() 'When the...
by: Toireasa | last post by:
Hi, Newbie Access developer here, and my first post on this forum, so I might not get everything right - thanks in advance for your help and your...
by: luciegiles | last post by:
Hi, I have used the tutorial Cascading Combo/List Boxes to filter the combo box cboCareManager dependent on the entry to cboLocalityTeam - the...
by: slenish | last post by:
I have a new problem that I am playing with involving the combo boxes. Alright now im going to try to explain this as best I can and hope (NeoPa)...
by: MarkP | last post by:
Hello, I have a combo box that feeds a list box1 which in turn feed another list box2. List box2 values need to remain in list box2 when list box1...
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.