the Manx Man's MS Access Pages

Having a List Box based on another List Box (016)

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 combo boxes cboMain and cboSub. (note that I did not put the MainID field into cboSub)

Here are the properties of the list boxes.

Name cboMain
Row Source

SELECT DISTINCTROW [tblMainItems].[MainID], [tblMainItems].[MainName] FROM [tblMainItems];
Column Count 2
Column Widths 0";1"
Bound Column 1
Name cboSub
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 cboMain and have cboSub show only items that match the choice made in cboMain.

There are two steps remaining.

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

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

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

        cboSub.Requery

 

e-mail the manx man index of tips click here for a laugh profit from the web

tip # 016  ||  previous tip  ||  next tip