473,289 Members | 2,155 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,289 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 64348
NeoPa
32,554 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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
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: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
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...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.