Stuck With Cascading Combooxes

[skilche1]

OK, I so I am trying to filter one combbox tbltCatch from tblLocation (which is a sub filter from tblLocationCategory). Thing is, I am trying to select "Catches" filtered from the tblLocationCategory table. Reason being is because the "Catches" are the same the location types (example below). I know the code I am trying to utilize is incorrect and needs to be modified.

    Dim sCatchLocation As String
    
    sCatchLocation = "SELECT [tblCatches].[Catches_ID], [tblCatches].[LocationCategory_ID], [tblCatches].[Catches] " & _
                        "FROM tblCatches " & _
                        "WHERE [tblCatches].[LocationCategory_ID] = " & Me.cboLocal.Value
                       Me.cboCatches.RowSource = sCatchLocation
    
    Me.cboCatches.Requery
    Me.Refresh

Any help would be grateful. Thanks

:banghead:

 

[pbaldy]

Looks okay offhand. Where is the code (what event)? Is the combo really named cboLocal? I'd expect Location_ID based on the picture, or cboLocation_ID based on the other combo.

 

[skilche1]

Looks okay offhand. Where is the code (what event)? Is the combo really named cboLocal? I'd expect Location_ID based on the picture, or cboLocation_ID based on the other combo.

I have the code in the AfterUpdate event

So I am trying to populate the cause by the Group location, not by a specific location as the specific locations will have the identical causes from the location groups.

 

[pbaldy]

Which after update event? Can you post the db here?

 

[skilche1]

Which after update event? Can you post the db here?

Yea, give me a few to rid the data

 

[skilche1]

Which after update event? Can you post the db here?

Here it is. Left the data in it as there is nothing revealing in it.

Thanks pbaldy

 

[pbaldy]

I must be misunderstanding the goal, because changing the location appears to correctly change the options available in the catches combo.

 

[skilche1]

hmmm, ok. Let me see if I can clarify the goal.

I have a table that contains only general locations (tblLocationCategories). I have another table that contains specific locations (tblLocations) that grouped into general locations. IE: Final Test is broken down to Final Test-QL, Final Test-RB, Final Test-QL, etc... | Packing broken down to Packing-QLoffices, Packing-RB, Packing-QL, etc, Now, there are different inspection criterias for each Location Categories although the inspection criterias are are the same between the actual Location (being the suffixes: -QL, -RB, -QL, ect).

My goal is when I select the Location (cboLocal), Catches will populate the quality issues from the tblLocationCategories table. Bypassing the tblLocation table.

Currently when I select Location, then Catches, the Catches are properly populated for the first 5 listed in the Location dropdown box (Assembly, CS Review, Cutting-Ext, Cutting-Fab, and Final Test-FB). I am wondering why and what I am doing wrong...

Hope this helps.

Thanks

 

[pbaldy]

There are only 5 different locations in tblCatches, so only those would return results (163 records, but only 5 different locations). Sounds like you need to add some data to that table.

 

[skilche1]

There are only 5 different locations in tblCatches, so only those would return results (163 records, but only 5 different locations). Sounds like you need to add some data to that table.

If you go into the tblLocationCategory table and change the Subdatasheet Name to Table.tblCatches, save it and open it. You should see catches associated with all 6 Location Categories. Let me know if you do not see that.

Thanks for your time man.

 

[pbaldy]

Yes I do (5 locations), though frankly it's irrelevant based on what you have. Your location table has 19 records, leaving 14 that if chosen from the locations combo will result in zero records in the catch combo. You could base your location combo on tbllocationCategory if you want to limit it to those with records in that table.

 

[skilche1]

You could base your location combo on tbllocationCategory if you want to limit it to those with records in that table.

I could, but then I wouldn't be sort, create reports and graphs by Location (specific locations)... My reports would be too vague...

Is there a way of doing what I am trying to achieve?

 

[skilche1]

I did try to create a query that includes all the data and that didn't seem to work either.

 

[pbaldy]

So what do you want to see in the catches combo if you select "Packing-DK" from the location combo, given that it has no records in the junction table? Or do you not want to see it as a choice in the location combo?

 

[skilche1]

When I select Packing-DK, I'd like to be able to select (Note, these would also show up when if I were to select and other with a prefix of "Packing-") The other selections of course would see other causes to select from.:

Bad BB Weld
Bad Stitch
BB Missing Wool Pile
BB too Long
BB too Short
Bent Brackets
Brackets Missing Bumpers
Contamination in Weld
Dirt on Material
Discolored Thread
Fascia Missing Wool Pile
Fascia too Long
Fascia too Short
Fraying on Ends of Material
Grease on Material
Hole(s) in Material
Incorrect BB Style
Incorrect BB Type (Paint or Wrap)
Incorrect Bottom Rail - HC
Incorrect Bracket Color
Incorrect Bracket Size
Incorrect Clutch Position
Incorrect Cutting Length Units
Incorrect Fascia Color
Incorrect Fascia Style
Incorrect Headrail - HC
Incorrect Material Color
Incorrect Roll Direction
Jagged Edge Cut
Missing Bracket
Missing Extra Bracket(s) - HC
Missing Pre-Winder Wrench
Missing Remote
Missing Remote & Wall Plate
Paint Chip on BB
Paint Chip on BB
Puckering Above BB
Puckering Above BB
QMotion Label on Pella Shade
Scratches on BB
Scratches on BB
Stain on Back of Material
Stain on Front of Material
Too Many Remotes

 

[skilche1]

What do you think pbaldy?

 

[skilche1]

OK, I seem to have resolved my issue. I basically did a work-around where I added a ListBox where I can reference back to the LocationCatrgory. Everything works except for one small issue. That is when I select the Location and Catch, it doesn't seem to show the value on some when I move between records...

Anyone have an idea what that occurs?