Query for Cascading Combo Boxes (1 Viewer)

Dangerous

Registered User.
Local time
Today, 14:58
Joined
Oct 18, 2018
Messages
73
I'm trying to create cascading combo boxes but can't get it to work correctly.

I've followed the video by Richard Rost where the second combo box only lists the cities based on the state selected in the first combo box.

It fails to filter the second combo box list but works in his demo.

I have a main table & form called 'Finds' with controls called 'Cat' & 'Scat' to store the valves entered.

I have a 'Category' table with 'ID' (number) & a control 'Cat' (category name) also
I have a 'SubCategory' table with 'ID' (number) & a control 'SubCat' (category name) & control 'FindsCat'

Combo box 'Catcbo' for the category and 'SCatcbo' for the subcategory.

Both combo boxes display as they should as individuals but when I add the 'AfterUpdate' event for the 'Catcbo' combo as 'SCATcbo.Requery' the 'SCatcbo' box still lists all entries in the 'subcategory' table rather than only those that have the same data in 'FindsCat' ans selected with the 'Catcbo' combo.

I've created a 'SubCatSort' query based of Richard's query.

I suspect the problem it the query but several examples I've found use the same query structure. I even recreated Richard's example with the same names etc. and mine failed.

Where am I going wrong? Access 2007
 

Ranman256

Well-known member
Local time
Today, 10:58
Joined
Apr 9, 2015
Messages
4,339
combo 2 source uses combo1 as criteria in the query
but you must refresh combo2 when combo1 is chosen....

cboStates has all state names. When user picks a state, the cboCity must be refreshed
cboCity.rowsource = qsCities1State
sql= select City from tCities where [state]=forms!myForm!cboState


Code:
'----------------
sub cboState_Afterupdate()
'----------------
cboCity.requery
end sub
 

Dangerous

Registered User.
Local time
Today, 14:58
Joined
Oct 18, 2018
Messages
73
combo 2 source uses combo1 as criteria in the query
but you must refresh combo2 when combo1 is chosen....

cboStates has all state names. When user picks a state, the cboCity must be refreshed
cboCity.rowsource = qsCities1State
sql= select City from tCities where [state]=forms!myForm!cboState


Code:
'----------------
sub cboState_Afterupdate()
'----------------
cboCity.requery
end sub

I have the requery code in the afterupdates event and it doesn't work.

Tried this instead SELECT [SCatT].[ID], [SubCategory].[SCat] FROM SubCategory ORDER BY [SCat]; I think that made combobox2 come up empty
 

Dangerous

Registered User.
Local time
Today, 14:58
Joined
Oct 18, 2018
Messages
73
It's when I use select [SubCat] from SubCategory where [FindCat]=forms!Finds!Fcatcbo in the row source the 2nd combo box shows empty
 

Ranman256

Well-known member
Local time
Today, 10:58
Joined
Apr 9, 2015
Messages
4,339
if the combo is bound to the ID, then the combo1 MUST use ID.
States dont need an ID field, since they are unique.
 

Dangerous

Registered User.
Local time
Today, 14:58
Joined
Oct 18, 2018
Messages
73
if the combo is bound to the ID, then the combo1 MUST use ID.
States dont need an ID field, since they are unique.

I need to store the value Either as the ID number or as Text. I'm not bothered which. I could make both the Category & SubCatergory data unique.
 

isladogs

MVP / VIP
Local time
Today, 14:58
Joined
Jan 14, 2017
Messages
18,186
Although it is very kind of Mike to (I assume) offer to check out your database, I'd just like to point out that normally it is better to upload to the forum so you can get the benefit of the wisdom of more than one person.
In addition it means that others can learn from both the problem and the solution
 

mike60smart

Registered User.
Local time
Today, 14:58
Joined
Aug 6, 2017
Messages
1,899
Hi Everyone

I have taken a look at the Cascading Combobox problem.

The error was in the construction of the SubCategory table.

I changed this:-

I have a 'SubCategory' table with 'ID' (number) & a control 'SubCat' (category name) & control 'FindsCat'

To this:-

subCategoryID - Autonumber
CategoryID - Number - FK to the Category table
SubCat - Text - (list of values related to the Category Selected)

Then in the after update of the Category Combobox I used this:-

Code:
Private Sub cboCategory_AfterUpdate()

    On Error GoTo cboCategory_AfterUpdate_Error
Dim sSource As String

sSource = "SELECT SubCategory.SubCatID, SubCategory.CategoryID, SubCategory.SubCat " & _
"FROM SubCategory " & "WHERE [CategoryID] = " & Me.cboCategory

Me.cboSubCategory.RowSource = sSource

    
    On Error GoTo 0
    Exit Sub

cboCategory_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboCategory_AfterUpdate, line " & Erl & "."

End Sub
 

Dangerous

Registered User.
Local time
Today, 14:58
Joined
Oct 18, 2018
Messages
73
Although it is very kind of Mike to (I assume) offer to check out your database, I'd just like to point out that normally it is better to upload to the forum so you can get the benefit of the wisdom of more than one person.
In addition it means that others can learn from both the problem and the solution

OK, I'll upload. Thanks for the info.

Just trying to but vBulletin Message Security Token Missing?
 

Dangerous

Registered User.
Local time
Today, 14:58
Joined
Oct 18, 2018
Messages
73
Mike has edited the DB to make the combo boxes to cascade correctly. Thanks to everyone for their help and suggestions and especially to Mike.
 

Users who are viewing this thread

Top Bottom