Add a 3rd combo box

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

Guest

I realize synchornized combo boxes has been an over-discussed topic in this
group. But I don't see any thread that reflects my need here and would really
appreciate if I can just get the extra 3rd combo box to work.

I now have 2 synchronized combo boxes (cboCategory and cboType) in a form
and now need a 3rd one (cboSize), but don't know how this could be done.

The RowSource of cboCategory is SELECT DISTINCTROW [CategoryID], [Category]
FROM tblCategory ORDER BY [Category]. -- tblCategory is a table with a list
of all categories.
The RowSource of cboType is from a query named qryAllProducts --
qryAllProduct is like a master datasheet and has records of all products
(incl. fields of Category, Type and Size).

Currently in this 2-combo-box set, Type is restricted by the selection of
Category. But I was hoping to expand this filter system by adding a 3rd combo
box, so that Size is restricted again by Type.

e.g. Now if I select "pants" in cboCategory, only things like "jeans,
trousers, etc" appear in cboType. How could I add a cboSize, so that if I
select "jeans" then only available jeans sizes (recorded in Size field of
qryAllProducts) are displayed in cboSize?

' --- start of Form module code ---
Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" tblType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub
' --- end of module code ---
 
The procedure for the 3rd combo is the same as for the second. The size
table should have the ID of the items that the sizes are for. You would
limit the 3rd combo using this ID as the parameter for its Row Source query.
What is a little different, it that you may want to clear the 3rd combo when
you make a selection in the first combo and requery the second combo.
 
Thanks Wayne, so I'd need to creat a size table with type ID (just like the
existing type table with category ID)? I'm embarassed to say that I'm
self-taught using Access manual and have no experience in Access coding.
Would you mind write out an example code if it wasn't too much trouble? *~*"

This is what I've tried, please correct me if neccssary:

' --- start of Form module code ---
Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" tblType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
Me.cboSize.RowSource = "SELECT Size FROM" & _
" tblSize WHERE TypeID = " & Me.cboType & _
" ORDER BY Size"
Me.cboSize = Me.cboSize.ItemData(0)
End Sub

Private Sub cboSize_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Size = '" & cboSize & "'"
End Sub
' --- end of module code ---


Wayne Morgan said:
The procedure for the 3rd combo is the same as for the second. The size
table should have the ID of the items that the sizes are for. You would
limit the 3rd combo using this ID as the parameter for its Row Source query.
What is a little different, it that you may want to clear the 3rd combo when
you make a selection in the first combo and requery the second combo.

--
Wayne Morgan
MS Access MVP


Sam Kuo said:
I realize synchornized combo boxes has been an over-discussed topic in this
group. But I don't see any thread that reflects my need here and would
really
appreciate if I can just get the extra 3rd combo box to work.

I now have 2 synchronized combo boxes (cboCategory and cboType) in a form
and now need a 3rd one (cboSize), but don't know how this could be done.

The RowSource of cboCategory is SELECT DISTINCTROW [CategoryID],
[Category]
FROM tblCategory ORDER BY [Category]. -- tblCategory is a table with a
list
of all categories.
The RowSource of cboType is from a query named qryAllProducts --
qryAllProduct is like a master datasheet and has records of all products
(incl. fields of Category, Type and Size).

Currently in this 2-combo-box set, Type is restricted by the selection of
Category. But I was hoping to expand this filter system by adding a 3rd
combo
box, so that Size is restricted again by Type.

e.g. Now if I select "pants" in cboCategory, only things like "jeans,
trousers, etc" appear in cboType. How could I add a cboSize, so that if I
select "jeans" then only available jeans sizes (recorded in Size field of
qryAllProducts) are displayed in cboSize?

' --- start of Form module code ---
Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" tblType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub
' --- end of module code ---
Click to expand...
Click to expand...
 
Actually, what you have written looks as if it should work. The only
question I would have would be when you start a new selection process. If
you select from Category, the Size combo box will still have the previous
selection in it. Taking what you have, you may want to add one line.

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" tblType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
'Try adding this line. You've made a selection in the line above,
'but the AfterUpdate won't fire
'for selections made through code. This line will run it.
cboType_AfterUpdate
End Sub

The other thing to consider is what do you want to do if an item doesn't
have a size option.
 
Thanks, Wayne.
I'm now creating a new table named tblSize for the 3rd combo. This tblSize
has fields of SizeID (generated by AutoNumber and set as primary key), Size
(NA, ALL, 12, 16, 18) and TypeID(same as TypeID already created by AutoNumber
in tblType).

However, unlike the one-to-many relationship between category and type
(where each type would only fall under one particular category, ie. jeans
must be under pants category and not under shirt or shoes), each type would
have the same size-set (NA,..,18).

What I did in tblSize is: having several repeatitions of the size-set
records in the Size field, and in the TypeID field next to each size-set
records is the TypeID of product type.

But this would create a huge tblSize (with 5 times the records than tblType
because each type has the same size-set of 5 sizes) , what would be a more
appropriate practice than what I've proposed? Would this be a many-to-many
relationship between type and size? Thanks
 
If the same sizes are available for everything, it could be set up as a
many-to-many relationship. However, it would be unusual for all of the
clothing to have the same sizes available unless you are making the clothes
yourself so that you are also generating the sizing. For example, shirts and
pants probably don't use the same sizing scheme, or if they do some of the
time, they probably don't always use the same one. Shirts may come as 12,
16, 18, etc or they may come as L, M, S, etc.

If they don't all use the same sizing scheme, I don't know any good way
around it other than a table with a lot of data, as you indicated. One
possible way around it would be to create a yes/no field for each possible
size then simply check the ones that are applicable to that type. If you do
this, it would be a one-to-one relationship and these fields could therefore
be placed in the same table as the type instead of a different table. The
problem with this is that in order to use it as you are trying to in a third
combo box, you would have to "reverse flatten" the data to get the checked
values back out in a vertical column instead of a row. This would probably
be a real pain to do.

Unless you can group things that have the same sizing scheme and use that to
link in between the type and sizes, you're going to have a lot of size
records.
 
Thanks, Wayne. You're super!
Yes, to make life easier, I use the same sizing scheme for all products. So
as you suggested, instead of creating a huge table I proposed before, I'll
just have a simple size table (tblSize) with 2 fields of SizeID and Size, and
the size range is still NA, ALL, 12, 16, 18. This table would be the
RecordSource of my 3rd combo. And if the size isn't available, the form would
just have to appear as blank (I've thought about having a popup message
asking user to select a different size, but I'm unable to write out such a
code with my limited coding knowledge)

Anyway, the problem is, because the form is based on a different query
(qryAllProducts), this 3rd combo will not filter records in the form. Also,
with the code I have below, I don't think the 3rd combo (if it works) would
further filter records already filtered by the previous 2 combos. How should
my code be altered? Please help

The RowSource of cboCategory is SELECT DISTINCTROW [CategoryID], [Category]
FROM tblCategory ORDER BY [Category]. -- tblCategory is a table with a list
of all categories.
The RowSource of cboType is from a query named qryAllProducts --
qryAllProduct is like a master datasheet and has records of all products
(incl. fields of Category, Type and Size).

--- start of form module code ---
Private Sub cboCategory_AfterUpdate()
' Display only types under selected category in cboCategory
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub

Private Sub cboSize_AfterUpdate()
' This further filters records under selected size
' But I don't think this works, please help
Me.Filter = "[Size] = '" & Me![cboSize] & "'"
Me.FilterOn = True
End Sub
--- end of form module code ---
 
You say that qryAllProducts has the Category, Type, and Size fields in it.
If that is the case, then after each successive combo box selection, modify
the filter with an AND statement to include the previous restrictions. This
would make the filter, after applying the size, look something like:

DoCmd.ApplyFilter , "Type = '" & cboType & "' And [Size] = '" & Me![cboSize]
& "'"

This will put both filtering constraints into the same filter. You could add
a 3rd or 4th item also, if needed.

--
Wayne Morgan
MS Access MVP


Sam Kuo said:
Thanks, Wayne. You're super!
Yes, to make life easier, I use the same sizing scheme for all products.
So
as you suggested, instead of creating a huge table I proposed before, I'll
just have a simple size table (tblSize) with 2 fields of SizeID and Size,
and
the size range is still NA, ALL, 12, 16, 18. This table would be the
RecordSource of my 3rd combo. And if the size isn't available, the form
would
just have to appear as blank (I've thought about having a popup message
asking user to select a different size, but I'm unable to write out such a
code with my limited coding knowledge)

Anyway, the problem is, because the form is based on a different query
(qryAllProducts), this 3rd combo will not filter records in the form.
Also,
with the code I have below, I don't think the 3rd combo (if it works)
would
further filter records already filtered by the previous 2 combos. How
should
my code be altered? Please help

The RowSource of cboCategory is SELECT DISTINCTROW [CategoryID],
[Category]
FROM tblCategory ORDER BY [Category]. -- tblCategory is a table with a
list
of all categories.
The RowSource of cboType is from a query named qryAllProducts --
qryAllProduct is like a master datasheet and has records of all products
(incl. fields of Category, Type and Size).

--- start of form module code ---
Private Sub cboCategory_AfterUpdate()
' Display only types under selected category in cboCategory
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub

Private Sub cboSize_AfterUpdate()
' This further filters records under selected size
' But I don't think this works, please help
Me.Filter = "[Size] = '" & Me![cboSize] & "'"
Me.FilterOn = True
End Sub
--- end of form module code ---


Wayne Morgan said:
If the same sizes are available for everything, it could be set up as a
many-to-many relationship. However, it would be unusual for all of the
clothing to have the same sizes available unless you are making the
clothes
yourself so that you are also generating the sizing. For example, shirts
and
pants probably don't use the same sizing scheme, or if they do some of
the
time, they probably don't always use the same one. Shirts may come as 12,
16, 18, etc or they may come as L, M, S, etc.

If they don't all use the same sizing scheme, I don't know any good way
around it other than a table with a lot of data, as you indicated. One
possible way around it would be to create a yes/no field for each
possible
size then simply check the ones that are applicable to that type. If you
do
this, it would be a one-to-one relationship and these fields could
therefore
be placed in the same table as the type instead of a different table. The
problem with this is that in order to use it as you are trying to in a
third
combo box, you would have to "reverse flatten" the data to get the
checked
values back out in a vertical column instead of a row. This would
probably
be a real pain to do.

Unless you can group things that have the same sizing scheme and use that
to
link in between the type and sizes, you're going to have a lot of size
records.
Click to expand...
Click to expand...
 
Back
Top