help

  • Thread starter Thread starter misschanda via AccessMonster.com
  • Start date Start date
M

misschanda via AccessMonster.com

I want to design a cascasding combo box.
ive tried to design before, but met much difficulty.

The design would allow one to choice a main category-->than a sub category---
than an Abbreviation---> than a material matching that abbreviation.
Click to expand...

i have already made five different queries (materials based on the four main
categories) and another with the materials linked to the abbreviation.

from previous post, i am aware of needed to make more than one combo box.
however, somehow all my queries are not linking to a final choice.

any help is appreciated
thanks
la
 
I will assume that you have the following tables:
tbl_Categories (Cat_ID, Cat_Desc, SubCat_ID, SubCat_Desc)
tbl_Abbreviations (Abbr_ID, Abbr_Desc)
tbl_Material (Mat_ID, Mat_Desc)

Then you need some sort of linking table that establishes the linkages
between SubCat_ID, Abbr_ID, and Mat_ID.

Next you need to add 4 combo boxes to your form (cbo_Cat, cbo_SubCat,
cbo_Abbr, cbo_Material)

1. The query behind cbo_Cat should look like:

SELECT DISTINCT Cat_ID, Cat_Desc
FROM tbl_Categories
ORDER BY Cat_Desc

Set the Bound Column to 1, and the ColumnWidths property to 0, 2 (the second
width value should be large enough to display all of the Cat_Desc field).

The afterUpdate Event of cbo_Cat should look like:

Private Sub cbo_Cat_AfterUpdate

me.cbo_SubCat.requery
me.cbo_SubCat = Null
me.cbo_Abbr.requery
me.cbo_Abbr = Null
me.cbo_Material.requery
me.cbo_Material = Null

END SUB

2. the query behind cbo_SubCat should look like:

SELECT Distinct SubCat_ID, SubCat_Desc
FROM tbl_Categories
WHERE Cat_ID = Forms!yourFormName.cbo_Cat
ORDER BY SubCat_Desc

Again, set the Bound column to 1 and the column widths to 0 and something
wide enough for the SubCat_Desc field. The AfterUpdate event of cbo_SubCat
should look like:

Private Sub cbo_SubCat_AfterUpdate

me.cbo_Abbr.requery
me.cbo_Abbr = Null
me.cbo_Material.requery
me.cbo_Material = Null

END SUB

3. The query behind cbo_Abbr will be a little different, because it will
use the table that links subcat, abbr, and material (assuming there is such a
table). It should look like:

SELECT DISTINCT tbl_Abbr.Abbr_ID, tbl_Abbr.Abbr_Desc
FROM tbl_Abbr
INNER JOIN tbl_OtherTable
ON tbl_Abbr.Abbr_ID = tbl_OtherTable.Abbr_ID
WHERE tbl_OtherTable.SubCat_ID = Forms!yourFormName.cbo_SubCat

Again set the bound column to 1, and the column widths as appropriate. The
AfterUpdate event would look like:

Private Sub cbo_Abbr_AfterUpdate

me.cbo_Material.requery
me.cbo_Material = Null

END SUB

Depending on what you are doing with this information, each of these combo
boxes should probably be bound to a field in your forms data source.

HTH
Dale
 
This Linking Table is it made like a regular table w/ field names being
subcat_id, abbr_id, and Mat_id or is it from a query?

Dale said:
I will assume that you have the following tables:
tbl_Categories (Cat_ID, Cat_Desc, SubCat_ID, SubCat_Desc)
tbl_Abbreviations (Abbr_ID, Abbr_Desc)
tbl_Material (Mat_ID, Mat_Desc)

Then you need some sort of linking table that establishes the linkages
between SubCat_ID, Abbr_ID, and Mat_ID.

Next you need to add 4 combo boxes to your form (cbo_Cat, cbo_SubCat,
cbo_Abbr, cbo_Material)

1. The query behind cbo_Cat should look like:

SELECT DISTINCT Cat_ID, Cat_Desc
FROM tbl_Categories
ORDER BY Cat_Desc

Set the Bound Column to 1, and the ColumnWidths property to 0, 2 (the second
width value should be large enough to display all of the Cat_Desc field).

The afterUpdate Event of cbo_Cat should look like:

Private Sub cbo_Cat_AfterUpdate

me.cbo_SubCat.requery
me.cbo_SubCat = Null
me.cbo_Abbr.requery
me.cbo_Abbr = Null
me.cbo_Material.requery
me.cbo_Material = Null

END SUB

2. the query behind cbo_SubCat should look like:

SELECT Distinct SubCat_ID, SubCat_Desc
FROM tbl_Categories
WHERE Cat_ID = Forms!yourFormName.cbo_Cat
ORDER BY SubCat_Desc

Again, set the Bound column to 1 and the column widths to 0 and something
wide enough for the SubCat_Desc field. The AfterUpdate event of cbo_SubCat
should look like:

Private Sub cbo_SubCat_AfterUpdate

me.cbo_Abbr.requery
me.cbo_Abbr = Null
me.cbo_Material.requery
me.cbo_Material = Null

END SUB

3. The query behind cbo_Abbr will be a little different, because it will
use the table that links subcat, abbr, and material (assuming there is such a
table). It should look like:

SELECT DISTINCT tbl_Abbr.Abbr_ID, tbl_Abbr.Abbr_Desc
FROM tbl_Abbr
INNER JOIN tbl_OtherTable
ON tbl_Abbr.Abbr_ID = tbl_OtherTable.Abbr_ID
WHERE tbl_OtherTable.SubCat_ID = Forms!yourFormName.cbo_SubCat

Again set the bound column to 1, and the column widths as appropriate. The
AfterUpdate event would look like:

Private Sub cbo_Abbr_AfterUpdate

me.cbo_Material.requery
me.cbo_Material = Null

END SUB

Depending on what you are doing with this information, each of these combo
boxes should probably be bound to a field in your forms data source.

HTH
Dale
I want to design a cascasding combo box.
ive tried to design before, but met much difficulty.
Click to expand...
[quoted text clipped - 11 lines]
thanks
la
Click to expand...
Click to expand...
 
Back
Top