Full Version: How to fill a Combo Box based on another Combo Selection
UtterAccess Forums > Microsoft® Access > Access Forms
RAZMaddaz
I want the User to be able to select something from a Combo Box and based on what is selected, have another combo box fill differently, based on what is chosen. For example: Say there are two combo boxes, one consisting of the US States and the other of the US cities. If the User selects New York from one Combo Box, I want the second Combo Box to fill with all the cities of New York. I was able to do a basic If statement, but I can only get the second Combo Box to fill with one so called city, and not show all the cities.

The whole idea, is to have whatever the User selects in the second Combo box added to a table with all the other related fields that are showing on the form too.

Thanks!!!!!!!
Roger
balaji
Search on this site for "cascading comboboxes". There are tons of references to this concept here.
annalyst
I tried searching but didn't come up with too many on cascading combo boxes, but I did get help here
cascading combo box
balaji
Glad you got it worked out. Good luck with the rest of your project.
RAZMaddaz
I didn't. I went over to the Candace Tripp web site and downloaded the two Cascade Combo Box examples. I read the code and I just can't believe there is not an easier way of doing this than that.
can currently read the other Combo Box and have the second Combo Box fill one item, by doing a simple If function, what more do I need to do with the IF statement to allow me to have multiple items display in the combo box at once as oppose to just one? Thanks!
Roger
balaji
I have not looked at the specific cascading combobox example on Candace's website, but the general concept is that you use the selection in the first combobox as a parameter in a query to select records for the next combobox. So, in your case if the user selects a state first and you want the second combobox to contain only cities in that state you would write the following in the rowsource of the second combobox:
Select cityname from locationtable where state = forms!myform.statecomboboxselection
Obviously, you have to change the names I have used in the above example to refer to things in your database.
RAZMaddaz
I will try that. Thanks!
Here is a small exaple of what the code is from the Candace website.
Private Sub cboRowField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboColumnField = Null
cboNumericField = Null

strSQL = "SELECT DISTINCT tblDemo.ColumnField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "'"
strSQL = strSQL & " ORDER BY tblDemo.ColumnField;"

cboColumnField.RowSource = strSQL

strSQLSF = "SELECT * FROM tblDemo2 "
strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "'"

Me!sfrmForm.LinkChildFields = "RowField"
Me!sfrmForm.LinkMasterFields = "RowField"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
balaji
This code seems identical to what I posted except that the SQL is generated in VBA instead of being written directly into the rowsource property. If modified correctly, it should work the exact same way my code would.
RAZMaddaz
Balaji, what you suggested almost works perfectly!!!!! When I choose a state, the relevant cities show in the second combo box. But if I CHANGE the State selection, the Cities in the second combo, does not get updated/changed. What kind of refresh or requery code do I need to add? Thanks again!
oger
RAZMaddaz
I figured out how to do it, by adding an On Got Focus Event and doing a Requery function to the second combo box. Make sense?
xample:
Private Sub City_Name_GotFocus()
Me.City_Name.Requery
End Sub
Thanks for all your help.
Roger
ScottGem
Yep, you have to requery the Cities combo after you change the State one. I would put the code in the After Update event of the City combo though. If you look thru the linked thread that Analyst gave you, it mentions that.
One other point. What Candace's example does is set the RowSousrce of the combo in code. there are some advantages to that, but it isn't the only way. But simply referencing the first combo in the query behind the second combo you accomplish the same thing.
balaji
Glad you got it worked out. Good luck with the rest of your project.
RAZMaddaz
Scott or Balaji, do either of you know of a reference book that consists only of all these VB codes, with examples for each. I used to have a Microsoft one for all the functions in Excel and that was great.
Scott, I added the code to the After Update event. Thanks!!!!
Roger
balaji
I have heard several people recommend the book by Grover Park George (look to the left of the posts). I learnt my access from the book Access 2000 programming for dummies and lots of trial and error (mostly the latter!). I browse through the help system of Access when I have some spare time and spend a lot of time on Utter Access to pick up more tips and tricks.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.