How to look up a value using three combo boxes (1 Viewer)

[bigalpha]

I've attached a screen shot of the relationships.

In the red circled tables, I'd like to use the PK from tblJewelryType, tblCollection, tblDesignName to look up the PK from tblJewelryInventory from three combo boxes on the form.

Each combination of those three PK's may have multiple "subtypes" in tblInventoryLink.

I'm trying to build a form that will be set up so after I select those three combo boxes, the subform for tblInventoryLink is narrowed down to only the specific subtypes that are available.

Do I have to make these cascading for me to accomplish my goal?

 

[Ranman256]

once the user picks the 1st collecton, you must requery the other combos.
cboCollection shows all collections
cboDesign show only designs using cbocollection
cboJewel only shows jewels in cboDesign

so the query for cboDesign will be like
select * from tData where [collection] = forms!frmFind!cboCollection

same with cboJewels...
select * from tData where [collection] = forms!frmFind!cboCollection and
[design] = forms!frmFind!cboDesign

then the form would filter all items with these choices.
me.filter = "[collection] ='" & cboCollection & "' and [design] = '" & cboDesign & "' and [jewel] = '" & cbojewel & "'"
me.filteron = true

 

[bigalpha]

once the user picks the 1st collecton, you must requery the other combos.
cboCollection shows all collections
cboDesign show only designs using cbocollection
cboJewel only shows jewels in cboDesign

so the query for cboDesign will be like
select * from tData where [collection] = forms!frmFind!cboCollection

same with cboJewels...
select * from tData where [collection] = forms!frmFind!cboCollection and
[design] = forms!frmFind!cboDesign

then the form would filter all items with these choices.
me.filter = "[collection] ='" & cboCollection & "' and [design] = '" & cboDesign & "' and [jewel] = '" & cbojewel & "'"
me.filteron = true

I have to admit that I've been working on this all morning. I have tried changing the tables so they support cascading combo boxes but I keep getting errors when trying to select data.

I'm not sure that I can adequately explain what I'm trying to do in words so I'm going to upload the db to help.

On frmJewelryInventory, I have three combos at the top in a group under "Inventory Item" (Collection Name, Design Name, Jewelry Type). These are the three I can not get to cascade correctly.

 

[bigalpha]

I think part of the problem is that the data in these combo boxes won't change very often and I'll be using the data in them over and over. Can I set up bound combo boxes so when I select the data in them, it's recorded?

For example, if I have Collection 1, Design 1, Jewelry 1; this can have multiple sub types (different metals and gems). Same with Collection 1, Design 1, Jewelry 2. How can I associate everything correctly without creating a bunch of useless records?

 

[Ranman256]

I think im still lost. When you drill down the 3 combos, is the form supposed to recall all records that match the combo?

or what do you want to happen when you pick the 3 combos?

 

[Ranman256]

I think im still lost. When you drill down the 3 combos, is the form supposed to recall all records that match the combo?

or what do you want to happen when you pick the 3 combos?

 

[bigalpha]

I think im still lost. When you drill down the 3 combos, is the form supposed to recall all records that match the combo?

or what do you want to happen when you pick the 3 combos?

My preferred action is that the three combos are cascading and after I select the three boxes, the subform is filtered to match the criteria.

 

[Ranman256]

then after the 3rd combo click FILTER button (to know when user is done)

me.filter = "[fld3]=cbo3 and [fld2]=cbo2 and [fld1] = cbo1
me.filterOn = true

 

[bigalpha]

then after the 3rd combo click FILTER button (to know when user is done)

me.filter = "[fld3]=cbo3 and [fld2]=cbo2 and [fld1] = cbo1
me.filterOn = true

Should I be storing the PK's for all three combo boxes somewhere, or just the last combo? I'm storing cbo1 PK in the table that supplies cbo2. cbo2 PK in the table that supplies cbo3 and the PK for cbo3 in a "main" table that has a few other data points.

Is setting a filter better or worse than using master/child links?