473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,324 developers and data experts.

Cascading Combo/List Boxes

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

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.
Feb 21 '07 #1
4 64368
NeoPa
32,556 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.

Kills
Apr 5 '11 #3
SwissProgrammer
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
Rabbit
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

5
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, cboPrgrp and cboSupplier. cboPrgrp is supposed to...
26
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 (in next drop list) ....then when i select that...
1
kcdoell
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 is throwing me off: Procedure declaration...
3
kcdoell
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 Division is selected, the appropriate Segment...
7
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 patience! I'm using Access 2007, in XP. I'm...
20
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 common code between the two tables is LocalityCode. ...
11
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) doesnt notice if I mess up :D This is going to be...
20
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 has been change by the combo. The content of list...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.