Full Version: Stuck With Cascading Combooxes
UtterAccess Forums > Microsoft® Access > Access Forms
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

What I do to cascade combos is write a query that generates a 'flat' view of all the valid combinations.
Then each of the cascades is a simple and very similar query over the 'flat' query.
Thanks for your reply. Been trying that, but I cannot see to get it to work...
Hello there Kilch,
It appears to me that the value in cboLocal will refer to 1 specific location, not it's location type.
This would mean that "WHERE [tblCatches].[LocationCategory_ID] = " & Me.cboLocal.Value is not a meaningful condition.
You might add LocationCategory_ID to the rowsource for cboLocal, as a hidden column with it's width set to zero.
Then you should be able to take the right value for comparison with something like "WHERE [tblCatches].[LocationCategory_ID] = " & Me.cboLocal.Column(1)

Also you could make the query a bit deeper, to draw locationcategory from the location table, something like this (air-code):
sCatchLocation = "SELECT [tblCatches].[Catches_ID], [tblCatches].[LocationCategory_ID], [tblCatches].[Catches] " & _
                 "FROM tblCatches INNER JOIN tblLocation ON tblCatches.LocationID = tblLocation.Location_ID " & _
                 "WHERE [tblLocation].[Location_ID] = " & Me.cboLocal.Value

This should yield all the catches registered for all locations in the category that cboLocal belongs to.

Then a little thing that struck me:
Your table/relations diagram shows a column named ExtrusionFinishDef, but the form shows a radio button labeled "External Finish Defect".
My intuition suggests that one of the two (extrusion or external) is a typograhic error.

Cheers, Daan cheers.gif
Hi Dean,

Sorry for the delay in responding to your post. I never got a notification (Wish the Admin would fix that). Thank you for catching Label labeling. I overlooked that.

Thank you for responding. I applied the code and I still have the same results. I have attached the db if you (or anyone else) care to look at it.

One thing I did change was: "FROM tblCatches INNER JOIN tblLocation ON tblCatches.LocationID TO "FROM tblCatches INNER JOIN tblLocation ON tblCatches.LocationCategory_ID

Again, thank you and it is appreciated...

sCatchLocation = "SELECT [tblCatches].[Catches_ID], [tblCatches].[LocationCategory_ID], [tblCatches].[Catches] " & _
                 "FROM tblCatches INNER JOIN tblLocation ON tblCatches.LocationID = tblLocation.Location_ID " & _
                 "WHERE [tblLocation].[Location_ID] = " & Me.cboLocal.Value
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?

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.