Form Filters

[kermit5]

I have a form that the user uses to select a hinge product available. Here is how the form is set up:

Step 1
The user inputs the Manufacturer from a list of available manufactures (based on q query that shows all hinge manufacturers in the price list)

Step 2
In the next field, the user selects a hinge series available from the manufacturer selected in step 1.

Step 3
The next field is the heights available for the hnge series selected in step 2. All hinges have a height.

Step 4
The width of hinge is selected from available widths for the hinge series selected in step 2. Not all hinges have a width designation.

Step 5
The finish of hinge is selected from available finish options for the specific hinge selected in step 2.

Here are the things I would like to do and the problems that I am having:

The hinges are appropriately narrowed down for the first hinge selected for a specific project. For example, if I select Stanley as the manufacturer of the first hinge I want to define, only the stanley options are available for steps 2 thru 5. The problem arises when I specify the second hinge. If I select Hager for the second manufacturer, the options for both Stanley AND Hager are available.

Here is my design.

Each Project has a file including a project name.

I then have a table [Project Hinge Specifications] in which the specific hinges are stored for a designated project using the form described above.

I also have a hardware price list that contains among other things a Mfr, Product Series, Product Width, Product Height, Product Finish and Product Price.

On my form, in the cmbHingeMfr field, the data source is a query for Product_Mfr from the tblProduct_Price_List. This value is stored in tblProjectHingeSpecification.HingeMfr.

The next field, cmbHingeSeries, the data source is a query linking tblProjectHingeSpecification.HingeMfr to the tblProduct_Price_List and using tblProduct_Price_List.ProductSeries.


How do I set the data source query for cmbHingeSeries, cmbHingeHeight, cmbHingeWidth, and cmbHingeFinish to use only the current record and not other records for the same project?

 

[jimbrooking]

Here's what I did in a similar application:

1. Make the top-level cmb (I call'em cbo's) have a rowsource query that lists the mfgrs.

2. Make the hinge series cbo work from a query that lists ALL hinge series WHERE mfgr = me.cboMfgr

3. Make the height cbo work from a query that lists ALL heights WHERE mfgr = me.cboMfgr AND Series = me.cboSeries

Etc. The RowSource for each cbo includes a WHERE clause that mentions all the cbo's on which the cbo depends.

You need to use the AfterUpdate event for ALL cbo's on which other cbo's depend. In it, do a requery on all dependent cbo's as well as all cbo's in the dependency chain from the AfterUpdate'd cbo.

HTH,
Jim