Select Case questions

[karbar]

Good Morning!
Thought this would be an easy one to figure out, but apparently not today

I have a country table (tblcountry), and then three tables with cities
(tblUS, tblUK, tblFrance) - my plan was to use cascading combo boxes...the
two I am working with are cboCountry and cboCity...I want cboCity to update
with only the table from the selected Country (a US city if US is chosen)

On my form, I have the following code in the AfterUpdate Event on my
cboCountry combobox:


Select Case cboCountry.Value
Case "France"
cboCity.RowSource = "tblFrance"
Case "United Kingdom"
cboCity.RowSource = "tblUnitedKingdom"
Case "United States"
cboCity.RowSource = "tblUnitedStates"
End Select

It seemed simple enough, but it doesn't work...I added a requery thinking
that was the problem. I suspect I am missing something simple here. Any
help or suggestions would be immensely appreciatted.....

 

[Klatuu]

The first thing you are doing incorrectly is having 3 tables where you need
only one. Put all 3 country's cities in one table and add a field that
designates the country.
Then in the row source of cboCity use a query that filters on the value in
cboCountry. All that would be required would be to requery cboCity in the
After Update event of cboCountry.

 

[RonaldoOneNil]

Does your countries table just contain the country name or does it have an
index or code. Is this index or code included in the combo box and is bound
to it and just displays the country text ?
Check is by placing a Msgbox cboCountry.Value just before your case statement.
To fix it change to Select Case cboCountry.Text

 

[Beetle]

Why do you have different tables for the cities?

What you should have is;

tblCountries
*********
CountryID (Primary Key)
CountryName

tblCities
******
CityID (PK)
CountryID (Foreign Key to tblCountries)
CityName


So the data would look like;

(tblCountries)
CountryID CountryName
1 France
2 United Kingdom
3 United States

(tblCities)
CityID CountryID CityName
1 1 Paris
2 2 London
3 3 New York


Then your city combo box would only need one rowsource like;
"SELECT CityID, CountryID, CityName FROM tblCities WHERE
tblCities.CountryID = " & Forms!YourForm!YourCountryComboBox

Then you would need to requery the City combo box in the After Upate
event of the Country combo box.

 

[Klatuu]

To fix it change to Select Case cboCountry.Text

Sorry, but that is incorrect. It would be in regular VB, but VBA is
different in this case. The Tex property only has value when the control
has the focus. Also, in VBA the default property in .Value, but you don't
have to specify the .Value property.

 

[karbar]

Thank you - I will be merging my data into one table and attempting this
again...it seemed "neater" to split it up, but it seems easier this way.

Thanks to Klaatu and Beetle!

 

[RonaldoOneNil]

I agree with what you are saying, but the code is in the combobox after
update event so how would it not have the focus when it fires ?

 

[Klatuu]

It would have the focus. What I am saying is that would not be a fix because
there is no difference between what was originally posted and what you
posted.

 

[Klatuu]

No, it would not be neater. You are doing the right thing.

 

[Evan]

I'm adding onto this post because I'm also using cascading combo boxes with
CityName appearing contingent on the selected CountryName. I used this code:

"SELECT CityID, CountryID, CityName FROM tblCities WHERE
tblCities.CountryID = " & Forms!YourForm!YourCountryComboBox

In my code modified as:
SELECT City.CityID, City.CityName, City.CountryID
FROM City
WHERE ((("City.CountryID = " & [Forms]![CountryMaster]![ComboBox8])<>False))

ORDER BY City.CityName;

but it asks me to enter parameter value: Forms!CountryMaster!Combo8.

What did I do wrong?

Evan