multiple cascading combo boxes* not the usual.

[Airwolf via AccessMonster.com]

Hello,
I am having a problem with multiple cascading combo boxes... yet this isn't
the usual situation thoroughly covered by many posts. I wasn't able to find
this elsewhere.
What I have is a category combo box than changes the products combo box-
this part is working fine. However what I want is a category combo box that
when updated, updates the three product combo boxes below.
What I am aiming at getting is to have the user select a major category and
up to three products within that category which will be stored into a table.
Is this the best option or is there a way to use a category combo box and a
multi-select for the products below?

Thanks in advance for any help you can provide,

 

[John W. Vinson]

What I have is a category combo box than changes the products combo box-
this part is working fine. However what I want is a category combo box that
when updated, updates the three product combo boxes below.
What I am aiming at getting is to have the user select a major category and
up to three products within that category which will be stored into a table.
Is this the best option or is there a way to use a category combo box and a
multi-select for the products below?

If you're storing the three products in three fields in a table... you're not
using good design principles! That's called "committing spreadsheet" -
repeated fields in a table.

Might it not be better to have a one-to-many relationship to a ProductsUsed
table? If the user needs to select two products, or three, or eleven, they can
add multiple *records* to the related table using a single dependent combo box
on a Subform.

John W. Vinson [MVP]

 

[Airwolf via AccessMonster.com]

Thanks John.
I agree, I've been trying to figure out ways to make it simple, knowing that
at some point trying to construct a report would be a pain if the data is
split. (I'm new to Access, which is why I am not sure of what can and can't
be done).

If I'm understanding correctly, my new question is: How would I tell the
second combo box (the one with the specific products) to write the "new
record(s)" into the ProductsUsed table?
Also, how would I code for the removal of a product using the combo box?

Thanks again,


...................

 

[John W. Vinson]

Thanks John.
I agree, I've been trying to figure out ways to make it simple, knowing that
at some point trying to construct a report would be a pain if the data is
split. (I'm new to Access, which is why I am not sure of what can and can't
be done).

A great deal can be done. Queries are *designed* to combine data from multiple
tables; reports are based on queries. Constructing a report is actually
*easier* from properly normalized data.

If I'm understanding correctly, my new question is: How would I tell the
second combo box (the one with the specific products) to write the "new
record(s)" into the ProductsUsed table?

You would have the combo box on a Subform bound to the ProductsUsed table. The
main form would be based on your current table (and I have no idea what that
table is or contains). This Subform would allow you to enter one, or two, or
sixteen rows, each for a different product.

Also, how would I code for the removal of a product using the combo box?

Simply delete that record.

It sounds like you're still thinking of a combo box as something that contains
data. IT DOESN'T. It's just a tool that lets you select a record from one
place (its Row Source) and put a value from that record someplace else (its
Control Source).


John W. Vinson [MVP]