|
|
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.....
|
|
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.
"karbar" <karbar[ at ]discussions.microsoft.com> wrote in message
news:68E1473B-A1AF-495D-A163-CC31B6F0E743[ at ]microsoft.com...
[Quoted Text] > 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.....
|
|
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
"karbar" wrote:
[Quoted Text] > 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.....
|
|
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.
--
_________
Sean Bailey
"karbar" wrote:
[Quoted Text] > 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.....
|
|
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.
"RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message
news:98B86D27-386B-4367-8E4F-25EE08576884[ at ]microsoft.com...
[Quoted Text] > 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
>
>
> "karbar" wrote:
>
>> 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.....
|
|
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!
"Beetle" wrote:
[Quoted Text] > 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.
>
> --
> _________
>
> Sean Bailey
>
>
> "karbar" wrote:
>
> > 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.....
|
|
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" wrote:
[Quoted Text] > 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.
>
> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message
> news:98B86D27-386B-4367-8E4F-25EE08576884[ at ]microsoft.com...
> > 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
> >
> >
> > "karbar" wrote:
> >
> >> 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.....
>
>
>
|
|
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.
"RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message
news:2195F0A7-8C91-4C6D-A236-BEE233179362[ at ]microsoft.com...
[Quoted Text] >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" wrote:
>
>> 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.
>>
>> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in
>> message
>> news:98B86D27-386B-4367-8E4F-25EE08576884[ at ]microsoft.com...
>> > 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
>> >
>> >
>> > "karbar" wrote:
>> >
>> >> 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.....
>>
>>
>>
|
|
No, it would not be neater. You are doing the right thing.
"karbar" <karbar[ at ]discussions.microsoft.com> wrote in message
news:0D1E6E67-502F-4691-B286-CB9AB0FC6A35[ at ]microsoft.com...
[Quoted Text] > 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!
>
> "Beetle" wrote:
>
>> 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.
>>
>> --
>> _________
>>
>> Sean Bailey
>>
>>
>> "karbar" wrote:
>>
>> > 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.....
|
|
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
"Beetle" wrote:
[Quoted Text] > 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.
>
> --
> _________
>
> Sean Bailey
>
>
> "karbar" wrote:
>
> > 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.....
|
|
|