DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

List Box based on another List Box

I notice this question in the newsgroups regularly.

"I have 2 list boxes.  I want the value I choose in the first one to control the data shown in the second one.  How?"
(p.s. the answer applies to combo boxes as well)

To try it out, I created a MainItems table and a SubItems table. There is a one-to-many relationship between the MainID field in the MainItems table and the MainID field in the SubItems table. The following shows both the structure and some sample data.

tblMainItems
- MainID  (primary key)
- MainName

tblSubItems
- SubID  (primary key)
- MainID
- SubName


tblMainItems (data)
1,  colours
2,  shapes
3.  animals
tblSubItems (data)
1,  1,  red
2,  1,  green
3,  1,  blue
4,  2,  circle
5,  2,  square
6,  2,  triangle
7,  3,  lion
8,  3,  tiger
9,  3,  elephant

Next is the form.

I created a simple unbound form named frmMyTestForm, and used the list box wizard to put 2 list boxes on the form. The first list box was based on the MainItems table and the second list box was based on the SubItems table. I named the listboxes lstMain and lstSub. (note that I did not put the MainID field into lstSub)

Here are the properties of the list boxes.

Name lstMain
Row Source

SELECT DISTINCTROW [tblMainItems].[MainID], [tblMainItems].[MainName] FROM [tblMainItems];

Column Count 2
Column Widths 0";1"
Bound Column 1
Name lstSub
Row Source

SELECT DISTINCTROW [tblSubItems].[SubID], [tblSubItems].[SubName] FROM [tblSubItems];

Column Count 2
Column Widths 0";1"
Bound Column 1

Both of these list boxes now work, however, they are not working together. The plan is to choose an item from lstMain and have lstSub show only items that match the choice made in lstMain.

There are two steps remaining.

(1)  Modify the SQL in lstSub to select only those sub items that have a MainID the same as the one chosen in lstMain. We do that this way. Change the Row Source to this:

SELECT DISTINCTROW [tblSubItems].[SubID], [tblSubItems].[SubName] FROM [tblSubItems]  WHERE (((tblSubItems.MainID) = [forms]![frmMyTestForm]![lstMain]));

(2)  To make sure that lstSub notices every time we change lstMain, add the following VBA code to a procedure created in the After_Update event of lstMain.

lstSub.Requery

 

(article continues after sponsor spot)



NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy