Conditional Drop Boxes

D

dme82

This is a basic example but it will give you an idea of what I am trying to
do.

I have four different size doors and four different colors. The following is
the color choices for each size.

SIZE
3870 White, Red, or Blue
3872 Orange
4270 Blue, or Orange
4272 White, Blue, or Orange

I want to create a form with a drop box (which is no problem) from which I
can choose a size. Next I want a drop box with the color choices based
conditionally on what is chosen in the size drop box (which is where I need
help). For example, if I choose size 3870 in the size drop box, I want the
second box to display color choices for only white, red, and blue. Or, if I
choose 3872, I only want the color choice orange.

Thanks for any help! It would be greatly appreciated!
 
D

Douglas J Steele

Conceptually, that should be represented as 3 tables: one for Size, one for
Color and one to represent the resolution of the many-to-many between Size
and Color.

You might be able to get away without having a separate Color table if you
really want, but you definitely need a table that returns one row for each
colour associated with a given size, rather than having them as a single
denormalized field.

Once you've got that setup, then you put code in the AfterUpdate event of
the first combobox to set the RowSource property of the second combobox,
like in http://www.mvps.org/access/forms/frm0028.htm at "The Access Web"
 
D

dme82

Ok...this is new ground for me. Say that I used one table for size and one
table for color. How do I go about creating the third table and where does
this table fit in the AfterUpdate code on the form? Thanks again!
 
D

Douglas J Steele

You should have a Size table with the following rows:

SizeId
3870
3872
4270
4272

You'd then have a SizeColor table with the following rows:

SizeId ColorNm
3870 White
3870 Red
3870 Blue
3872 Orange
4270 Blue
4270 Orange
4272 White
4272 Blue
4272 Orange

Assuming your first combobox is named cboSize, linked to the Size table, and
your second combobox is named cboSizeColor, you'd put code like the
following for the AfterUpdate event of cboSize:

Private Sub cboSize_AfterUpdate()

Dim strSQL As String

strSQL = "Select ColorNm "
strSQL = strSQL & "From SizeColor "
strSQL = strSQL & "Where SizeId " & cboSize
strSQL = strSQL & " Order By ColorNm"
Me!cboSizeColor.RowSourceType = "Table/Query"
Me!cboSizeColor.RowSource = strSQL

End Sub



The normal approach to maintaining the linkage between two tables is to
create a form based on one of the two tables, with a subform that's based on
the linkage table. On that subform, you'd have a combobox based on the other
of the two tables. Take a look at the Orders form in the Northwind database
that comes with Access: it includes Orders Subform. (The relationship
between Orders and Products is a many-to-many that's resolved by the Order
Details table)
 
Top