From: rnjalston on
I have 5 tables: Manufacturers, Products, Brands, Varieties, Sizes.

I would like a combobox Manufacturer that would filter a Products combobox
that would in turn filter a Brands combobox. After selecting a Brand from
the Brand combobox, I need to enter several Varieties for each Brand and
several Sizes (Size, Unit, Pkg) for each Variety.

Please help. I am new to this. I have the relations set in the order that
the tables appear on the 1st line of this post.

From: John W. Vinson on
On Thu, 11 Mar 2010 06:38:14 GMT, "rnjalston" <u58689(a)uwe> wrote:

>I have 5 tables: Manufacturers, Products, Brands, Varieties, Sizes.
>
>I would like a combobox Manufacturer that would filter a Products combobox
>that would in turn filter a Brands combobox. After selecting a Brand from
>the Brand combobox, I need to enter several Varieties for each Brand and
>several Sizes (Size, Unit, Pkg) for each Variety.
>
>Please help. I am new to this. I have the relations set in the order that
>the tables appear on the 1st line of this post.

This is a pretty common situation; the solution is what's often called
"conditional combo boxes".

On your Form, put a Manufacturer combo box (cboMfgr let's call it). It should
have the ManufacturerID as the bound column (it probably will anyway if you're
using it to update the manufacturer field in the form's recordsource).

Create a Query as the rowsource for the Product combo. I presume that the
Products table contains a ManufacturerID; if so, use

=[Forms]![YourFormNameHere]![cboMfgr]

as a criterion on the field. This will limit the Product combo box to products
from this manufacturer.

You will need one line of code, or a macro, in the AfterUpdate event of
cboMfgr; it needs to Requery cboProduct.

Repeat this process at each link of the chain.

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: form within a form
Next: Design View