Full Version: Using one combo box to filter another combo box
UtterAccess Forums > Microsoft® Access > Access Forms
NotACodeFan
I have had this problem for a while now and still haven't been able to crack it. I have a form with records of people's addresses. I want to organise the address so that when i click on country it limits the counties and when i click on counties it limits the city/town/village
I have seen at least 4 examples now of databases that achieve what i want but for the life of me i cannot replicate the concept and understand how it all fits togther. Every time i try and copy it it doesn't work. I think i can create the table structure correctly but i get stuck when i try to create a form with the combo boxes. I have used combo boxes for ages but i can't get it to work for this.
Any suggestions? By the way i am a complete dummy when it comes to Access jargon so treat like a thicko and everything will be sweet.
mccarirt
This is a very good link. Step by step instructions on how to accomplish a cascading combo box
http://fontstuff.com/access/acctut10.htm
NotACodeFan
Hey thanks very much. I haven't read the tutorial yet but it looks by far and away the most in depth i have seen so far and i'm sure it will solve my problem, cheers!
NotACodeFan
I had a look over that tutorial and although it looks helpful i couldn't get it to work for me. I have tried numerous guides for trying to solve this problem and yet i still can't seem to get it to work, i feel completely helpless and frustrated.

I think my problem is when i try and glue everything together. Replicating the tables is easy.

I have been using the wizard to create a form window and then using the wizard to bring in combo boxes. Sometimes tutorials have said to use ID fields in the tables and sometimes not. Maybe the way i am creating the combo box is wrong as that is never something that is explained and i could be doing that wrong as i think there are a few ways of making one (although some ways are probably wrong). I have made a combo box using the wizrad and i have also made one by clicking on the combo tool and then dragging a field from the list. Trouble is i can't drag a field from the list for the next box as the field list (whilst in the form) only displays the fields of one table.

I can make combo boxes for each field individually but i can't make them link even after pasting in the code for the after update event.

I really don't know what else to do it must be such a small thing. Any idea from what i said it could be or maybe there are common mistakes people make. Pleeeeeease help. crazy.gif

I have added one of my attempts to cascade combo boxes from a tutorial i found here...
http://www.applecore99.com/frm/frm031.asp

Edited by: NotACodeFan on Thu Nov 25 19:21:04 EST 2004.
Edited by: NotACodeFan on Thu Nov 25 19:23:48 EST 2004.
ScottGem
I've attached an adjusted version of your file that works. There are 2 keys to doing cascading combos. The first is you need to filter the 2nd combo by the value selected in the first. So you need to add the County field to the query behind the Town combo then set the criteria to the first combo. The next issues is to requery the second combo after making a selection in the first
TH
NotACodeFan
I think i understand what you have done here as i didn't have that code in the query window, i don't know how i missed that although i don't remember seeing it in the tutorials i have looked at.
I am picking up more of the Access lingo now; until the other day i had no clue what after update event was or what it did.
I'll give this another go when home from work and hopefully i should be able to make it work this time following your changes.
I'm really grateful that you took time to ammend my database for me and send it back with my original faulty one, that way i can compare the two.
Cheers
ScottGem
Access is an object oriented and event driven platform. This means that everything is an object that has properties. An object can be manipulated by changing its properties. One class of properties is events. Events allow you to perform an action when the event occurs. Between properties and events, you get Access to perform its magic.
NotACodeFan
Hi Scott
After you fixed my database and sent it back to me it has helped me in a massive way to understand how to create cascading combo boxes, that and A LOT OF HOURS!!! I hoped you would have a look at the latest version; I have been very successful in achieving my goal but there is one thing that has got me stumped.....
Ofound some code on a forum to automatically update combo boxes in the underlying table and i have done that successfully with some of the combos in the database attached. Some combos however, (despite having the same code written for them as the other successful combos) don't seem to be working, any idea why and how i could
fix this problem?
I really appreciate any further help you can give me on this.
ScottGem
Ok the reason this is not working is because you have to update more then the one field. For example, in the Counties combo (BTW, you should name your comboboxes more meaningfully). You also have to populate the StateID field, otherwise the option doesn't show on the filtered query. Add a line:
Fields("StateID") = Me!combo28
You will need to do similar for the others.
HTH
NotACodeFan
Ok i have named my combos more meaningfully so that's good. I did have some some entries in the tables that meant that the combo boxes all linked up but most likely when you had a look you did not see this as only about 2 addresses link up at the moment and it would be a real headache to make more addresses work without the aid of the auto update function working on all the combo boxes. I typed in Canada, British Columbia, then '-' (dash, because there is no county applicable for BC), then Vancouver and lastly Maple Ridge (the suburb). That all worked fine. By the way the state field is deliberately not reliant on the country combo but i won't go into why that is or i will end up writing a novel explaining it and probably get us both in a muddle. It's all to do with the fact that most countries don't have States within them.
couldn't work out where you wanted the code you added in your last post. Did you want it added to the foreign key field in the SQ thingy or did you want it in the field for the table 'State' in the SQ thingy? Well i tried both and neither helped, but i had no clue what the code was for anyway.
Do you know of a database that i could look at that has a series of cascading boxes with the notinlist autoupdate function already working so i could study it?
ScottGem
The line I gave you was an example. The point is this. When you are adding a record to the table where a foreign key is required you have to add the foreign key to the record as well. Otherwise, when you select that foregin key in another combo ther will be no match. The line of code goes after the line where you add the value you are adding. For example, in the NotInList event of the Counties combo, you would have:
Fields("County") = NewData
.Fields("StateID") = Me!combo28
HTH
NotACodeFan
I hope you are not sick of me yet. I have pasted the code below with the line you put in your last post. The code looks a bit different as i have now made all the tables into the one table "ALL" This seems a lot more logical. Even after adding the new code i still can't get the State combo to update. I have included the database as it stands now too.
oss
Private Sub ComboState_NotInList(NewData As String, Response As Integer)
' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf

' database and recordset object variables
Dim db As DAO.Database
Dim rs As DAO.Recordset

' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the current database
Set db = CurrentDb
Set rs = db.OpenRecordset("ALL")
' using the recordset object
With rs
.AddNew ' prepare to add a new record
.Fields("State") = NewData ' add unfound data into field
.Fields("State") = Me!ComboState
.Update ' update the table
.Close ' close the recordset object
End With
Response = acDataErrAdded ' confirm record added
Else
MeState.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If

Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler

End Sub
ScottGem
Ok, you need to look at my code and your code. Its NOT the same. What fields does your code update? What fields does my code update? Please reread my last post as it explains the logic you need to follow here.
also don't understand this ALL thing. What did you do there?
NotACodeFan
The reason i have the "All" table is that i saw another example of this puzzle and someone had done it with one table instead of many. I adopted it because it appeared to make it easier to understand. I have two versions of the database now. I tried the code you gave (recognising my error above) with the original database with the separate tables and i still can't get it to work. Also it seems as soon as i get over one problem another appears.
I have difficulty understanding the terminology with this whole thing and so maybe i should just stop asking for a solution because at this rate i'll be here till Christmas and you will have no patience left.
ScottGem
I'm still not following what All is. Can you post the structure?
And don't worry about trying our patience. If we didn't want to help we wouldn't be here.
NotACodeFan
Thanks for perservering. I have read and read again what you wrote about the missing code and i think i understand it, just. I am pretty sure i have done that correctly but there must be some other little thing i have done wrong.
created the table "All" as i said, to simplify things as i saw in another example. One thing i didn't like about what i had with mulitiple tables was that i had ID numbers and they were separated into different tables. I guess i could have changed the numbers for words but i have been working to the very limits of my Access understanding so not everything always goes smoothly and there is a considerable amount of guess work.
I've changed things and changed things again and no joy. Here is one of the new designs with the one underlying table as opposed to many tables. See what you make of it. I'll attatch the other design in another post but i prefer this one better. It hasn't got your coding in it but i did try it.
NotACodeFan
Here's the original design although i would prefer to hang on to the new design which is simpler and hopefully better
Edited by: NotACodeFan on Tue Nov 30 17:39:36 EST 2004.
Jack Cowley
Scott Gem appears to be out of the building at the moment so I willl jump in with my 3 cents worth... This db is closer to what you want than your db with the All table. As an example of what I mean lets use the USA and its 50 states. If you put in each state you would repeat USA 50 times in your ALL table. You would be better off to have your tables set up like this:
blCountries
CountryID (PK and auto)
CountryName
tblStates
StatesID (PK and Auto)
CountryID (FK)
StateName
tblCities
CityID (PK and auto)
StatesID (FK)
CityName
Now you can see that you can have a form based on Country, a subform based on States and a subform to that subform based on Cities. Selecting USA in the main form will show all the States in the subform. Selecting a State in the subform will show the Cities in that state.
Now I am not sure what your original question was, but this is just my 3 cents worth about normalizing your data...
I hope that I have not tossed a spanner into the works...
Jack
ScottGem
I don't know where you got this ALL idea, but it doesn't make sense. Please look at what Jack gave you. That would be a properly normalized structure.
The original problem was in adding data. You were using the Not In List event to add new data. When doing that, you need to add both the data you are entering and the foreign key. Otherwise there is no way to filter the combo for the previous value.
NotACodeFan
I have changed my table structures now

My understanding of how the not in list function worked was that you could add data to a combo that is not in the underlying table, information the database has never seen before. From what you are saying it sounds like if it is not in a table you can't add it.

Do i have to have every country, state, county, village/town/city, suburb before i can add new addresses in the combos (which are really just different combinations of data).

If that is so then the notintlist function is not as great as i thought.

The only way to really understand if i am still wrong is for you to actually adjust my database and send it back for me to study. Some of things you are saying I sometimes interpret in more than one way because i don't always fully understand what you are getting at and i don't have the language to accurately describe my problems either.
Edited by: NotACodeFan on Wed Dec 1 4:37:49 EST 2004.
ScottGem
No you misunderstand. The code you have WILL add a record to the underlying table. The problem you had is that you weren't populating the table completely.
et me see if I can explain this concept a little better. To use Cascading combos requires that there be a Foreign Key in the table the combo is querying. Without such a foreign key, there is nothing to filter on. So a Regions table (states, provinces, etc.) needs a country field as a FK. That FK needs to be populated with the PK value of the Country from the Country table. So that when you select a country, only those regions within that country will show up. If you don't populate the country field when you add a Region through the Not In List event, then that region will not show when you choose a country, since the country field doesn't match the selected country value.
Therefore, to filter a combo based on a previously selected value, the query behind the combo must include the foreign key field (it can be set not to Show) with the criteria set to the previous combo. If you want to use the NotInList event to add new values, you need to populate the record completely. This is another reason why your ALL idea doesn't work, because you don''t have enough info to populate the record properly.
I've attached your latest version which has three problems, 2 of which I fixed. The first problem was you had a StatesID field in the Country table. this was not needed since you aren't going to filter countries by states. The second problem was in your States Combo. You didn't have the Country filter so all states were showing up. However, you have the correct filters in the other combos.
The third problem is your tables aren't complete. You are missing the foreign key on bunches of records. These need to be filled in correctly for the cascade effect to work.
Otested the Not In List event on both the States and Counties combos and they worked fine.
HTH
NotACodeFan
Thanks for having a look and fixing a few things again. I think i see what you are getting at although i won't fully understand it until i have populated the fields like you said. I'll spend some time doing that and see how i get on. This whole thing has been like a tongue twister for the brain for me.
One thing though. If i choose USA for the country then i get all the states in the states combo as they all take the PK from USA in the Countries table but what about when the countries that don't have states? I have been using a dash "-" for those before you connected the country and state combos. Now that they are connected doesn't this cause problems? I mean as many countries don't have states then many Country PK numbers will need to be entered into the foreign key row for the relevant state (in this case a dash as those countries don't have states).
I have attached a diagram of what i mean although i'm sure you understand anyway. Am i right or have i overlooked something here. Before as i saw this problem i kept the Country and State combos separate
ScottGem
What I think I you need to do here is add a Yes/No field to the Country table call it HasStates. If that country has states (or provinces or whatever) mark it as Yes. Then add a CountryID to the County table, but only populate it when you want to filter the counties by country.
ext add the HasStates field as a column in query behind ComboCountry. In the After Update event of the Country combo, test to see if Has States is True. If it is, then you need to set ComboState to visible and the RowSource of ComboCounty to filter by State. If it isn't, then ComboState is made invisible and ComboCount gets filtered by Country.
I've done this in the attached sample. check the changes made to Country and County tables and the After Update event of ComboCountry.
HTH
NoahP
I'd do it a bit differently. I'd just add a state named 'N/A' or '-' for the given country and then just proceed normally. If you have places where printing N/A would be a problem, you could check for it there, as long as you were consistent for all countries without states or provinces.
TH
Noah
ScottGem
Hi Noah,
I thought of that, but I made the assumption if the States weren't being filtered by Country, then the counties might be. In that case, the RowSource of the County combo would need to be changed. I suppose it could be changed if the State is chosen as N/A, but I thought my way would eliminate the need for entering a state at all.
oing it your way, how would you deal with the situation the County would need to be filtered by Country?
NotACodeFan
Wow that's brilliant Scott i would have been scratching my head for ages trying to actually do what you explained if you hadn't actually done it. It's a heck of a lot easier to understand when i can look at it. I would have had little clue how to do the after event code though. I'll get busy populating the fields now and if it works i will finally be able to add these cascading combos to my proper database and use it properly.
NotACodeFan
It would seem i overlooked something. You know how there were many countries without states, well i forgot that the same problem would apply to the many states that don't have counties! blush.gif(
tried looking at what you did and applying it to the new problem but it wouldn't work for me blush.gif( Now when i type in England as a country not only the state dissapears but also the county. I am hoping a small change would fix it.
Private Sub ComboCountry_AfterUpdate()
If Me!ComboCountry.Column(2) Then
Me!ComboState.Visible = True
Me!ComboCounty.RowSource = "SELECT Counties.CountyID, Counties.County FROM Counties WHERE (((Counties.StateID)=Forms!Contacts!comboState)) ORDER BY Counties.County;"
Else
Me!ComboState.Visible = False
Me!ComboCounty.RowSource = "SELECT Counties.CountyID, Counties.County FROM Counties WHERE (((Counties.CountryID)=Forms!Contacts!comboCountry)) ORDER BY Counties.County;"
End If
Me!ComboState.Requery
End Sub
Private Sub ComboState_AfterUpdate()
If Me!ComboState.Column(3) Then
Me!ComboCounty.Visible = True
Me!ComboVTC.RowSource = "SELECT VTCs.VTCID, VTCs.VTC FROM VTCs WHERE (((VTCs.CountyID)=Forms!Contacts!comboCounty)) ORDER BY VTCs.VTC;"
Else
Me!ComboCounty.Visible = False
Me!ComboVTC.RowSource = "SELECT VTCs.VTCID, VTCs.VTC FROM VTCs WHERE (((VTCs.StateID)=Forms!Contacts!comboState)) ORDER BY VTCs.VTC;"
End If
Me!ComboCounty.Requery
End Sub
ScottGem
Assuming, that you Well, the first issue is the Column number for ComboState is 2, not 3. The columns count starting at 0. Since the CountryID is a no show, it doesn't enter into the count. Therefore, the 3 column will never be True. That should fix it.
NotACodeFan
I made an additional change and I think it is working now, just don't hold your breath....i may be back.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.