Cascading combo box for dummies!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone explain to me in simple terms how to populate a second list of
options depending on the first option? I will find this useful for my
database.
 
Thanks for the link.

I am almost there but it still isn't quite working. I have a main department
(deptA)and a sub-department(deptB).

DeptA has "categoryID" and "DeptA"
DeptB has "categoryID", "DeptA" and "DeptB"

The rowsource for the first combo box I have:

DeptA

The rowsource for the second combo box I have:

SELECT [DeptB].[DeptB] FROM DeptB WHERE
((([DeptB].[DeptA])=[Forms]![Form1]!DeptA));

I can understand the concept of what is going on but it still isn't quite
right.
 
I'm assuming that what you've shown what DeptA and DeptB have below is
supposed to be the fields in the tables.

What's the name of the first combo box: your SQL for the second combo
implies it's DeptA. Is that correct? What's the bound field for the combo
box. For what you have to work, it would have to be 2, so that referring to
the name of the combo box returns the value of the second column of the
selected row. If you can't make the bound column the 2nd column, try
changing your query to

SELECT [DeptB].[DeptB] FROM DeptB WHERE
[DeptB].[DeptA]=[Forms]![Form1]!DeptA.Column(1)

(the extra parentheses Access likes to put in aren't really required)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scubadiver said:
Thanks for the link.

I am almost there but it still isn't quite working. I have a main department
(deptA)and a sub-department(deptB).

DeptA has "categoryID" and "DeptA"
DeptB has "categoryID", "DeptA" and "DeptB"

The rowsource for the first combo box I have:

DeptA

The rowsource for the second combo box I have:

SELECT [DeptB].[DeptB] FROM DeptB WHERE
((([DeptB].[DeptA])=[Forms]![Form1]!DeptA));

I can understand the concept of what is going on but it still isn't quite
right.

Douglas J Steele said:
Take a look at
http://office.microsoft.com/en-us/assistance/HA011730581033.aspx
Click to expand...
Click to expand...
 
Hi,

I have spent some time working on this and I have almost got it. I have
rearranged the tables and column names into the following to make it easier.

Dept: categoryID, dept
Subdept: categoryID, dept, subdept

The first combo box I have called "dept" and the second I have called
"subdept". In the first combo box I have just selected "dept" and in the
second I have selected "dept" and "subdept".

The row source for the first combo is: SELECT [Dept].[Dept] FROM Dept;

The row source for the second combo box is:

SELECT [Subdept].[Subdept] FROM Subdept WHERE
((([subdept].[dept])=[Forms]![Form2]![dept]));

This works for only one slight problem. The second combo box list is
populated depending on the option in the first box when the form is first
opened. When I change the first option, the list in the second box doesn't
change.





Douglas J Steele said:
I'm assuming that what you've shown what DeptA and DeptB have below is
supposed to be the fields in the tables.

What's the name of the first combo box: your SQL for the second combo
implies it's DeptA. Is that correct? What's the bound field for the combo
box. For what you have to work, it would have to be 2, so that referring to
the name of the combo box returns the value of the second column of the
selected row. If you can't make the bound column the 2nd column, try
changing your query to

SELECT [DeptB].[DeptB] FROM DeptB WHERE
[DeptB].[DeptA]=[Forms]![Form1]!DeptA.Column(1)

(the extra parentheses Access likes to put in aren't really required)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scubadiver said:
Thanks for the link.

I am almost there but it still isn't quite working. I have a main department
(deptA)and a sub-department(deptB).

DeptA has "categoryID" and "DeptA"
DeptB has "categoryID", "DeptA" and "DeptB"

The rowsource for the first combo box I have:

DeptA

The rowsource for the second combo box I have:

SELECT [DeptB].[DeptB] FROM DeptB WHERE
((([DeptB].[DeptA])=[Forms]![Form1]!DeptA));

I can understand the concept of what is going on but it still isn't quite
right.

Douglas J Steele said:
Take a look at
http://office.microsoft.com/en-us/assistance/HA011730581033.aspx

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Can someone explain to me in simple terms how to populate a second list of
options depending on the first option? I will find this useful for my
database.
Click to expand...
Click to expand...
Click to expand...
 
You need to put code in the first combo box's "AfterUpdate" event to requery
the second combo box:

Me!MySecondComboBox.Requery

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scubadiver said:
Hi,

I have spent some time working on this and I have almost got it. I have
rearranged the tables and column names into the following to make it easier.

Dept: categoryID, dept
Subdept: categoryID, dept, subdept

The first combo box I have called "dept" and the second I have called
"subdept". In the first combo box I have just selected "dept" and in the
second I have selected "dept" and "subdept".

The row source for the first combo is: SELECT [Dept].[Dept] FROM Dept;

The row source for the second combo box is:

SELECT [Subdept].[Subdept] FROM Subdept WHERE
((([subdept].[dept])=[Forms]![Form2]![dept]));

This works for only one slight problem. The second combo box list is
populated depending on the option in the first box when the form is first
opened. When I change the first option, the list in the second box doesn't
change.





Douglas J Steele said:
I'm assuming that what you've shown what DeptA and DeptB have below is
supposed to be the fields in the tables.

What's the name of the first combo box: your SQL for the second combo
implies it's DeptA. Is that correct? What's the bound field for the combo
box. For what you have to work, it would have to be 2, so that referring to
the name of the combo box returns the value of the second column of the
selected row. If you can't make the bound column the 2nd column, try
changing your query to

SELECT [DeptB].[DeptB] FROM DeptB WHERE
[DeptB].[DeptA]=[Forms]![Form1]!DeptA.Column(1)

(the extra parentheses Access likes to put in aren't really required)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scubadiver said:
Thanks for the link.

I am almost there but it still isn't quite working. I have a main department
(deptA)and a sub-department(deptB).

DeptA has "categoryID" and "DeptA"
DeptB has "categoryID", "DeptA" and "DeptB"

The rowsource for the first combo box I have:

DeptA

The rowsource for the second combo box I have:

SELECT [DeptB].[DeptB] FROM DeptB WHERE
((([DeptB].[DeptA])=[Forms]![Form1]!DeptA));

I can understand the concept of what is going on but it still isn't quite
right.

:

Take a look at
http://office.microsoft.com/en-us/assistance/HA011730581033.aspx

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Can someone explain to me in simple terms how to populate a second list of
options depending on the first option? I will find this useful for my
database.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top