473,580 Members | 2,967 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cascading Combo/List Boxes

Rabbit
12,516 Recognized Expert Moderator MVP
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.Subto tal.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.Bac kColor.

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 64563
NeoPa
32,565 Recognized Expert Moderator MVP
A related article can be found at Example Filtering on a Form.
May 22 '08 #2
Killian Mangezi
2 New Member
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 New Member
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 Recognized Expert Moderator MVP
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
2502
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 populate once a selection is made for cboSupplier. When a selection is made in cboSupplier and I click the dropdown box for cboPrgrp, it will prompt...
26
4804
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 choice give another list (drop list).. so for example i select canada, from list of contries, then i would get list of the provinces and then i...
1
2664
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 does not match description of event or procedure having the same name. Basically have three tables: One for the Division location:
3
3975
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 list will 'display in the drop down list of CboSegment With Me! If IsNull(Me!cboDivision) Then
7
5826
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 currently trying to set up a whole pile of cascading combo boxes of different levels of complexity, so I started with the easiest set - and can't even...
20
4121
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. cboCareManager and cboLocalityTeam both sit within frmSub which in turn is a subform within frmMain. The AfterUpdate code is as follows: Private...
11
3927
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 a tie in to what I was doing last time with the combo boxes and trying to make the auto-fill and update. What im trying to do is make it so I can...
20
3140
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 box2 will be stored in a table. I guess this is 2 part question: Are there any examples out there on list box2 used as a collection point, and how...
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8296
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5349
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3790
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3806
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1394
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1118
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.