combo box advice

[praz]

I need to have a combo box which will display the countries
from(Country table) and then user would select the country, which would
display the cites for that particular country, in another combo box.

I have set the relation from the country table to the city table. The
problem is when I select a country from the country combo box the
relevant cities are not displayed on the city combo box. I assume since
the relationship is set as i select a country from the country combo
box the information in the city combo box would change......like the
way it happens in form and subforms....... that's my understanding i
might be wrong. Anyway i tried this it does not work.

Then i tried to change the control source for the city combo, where it
is a query which would pull up the cities for the selected country in
the country combo box.... this also doesn't seems to work

Actually what i need to do is, the user to select a origin country/city
and a destination country/city. For this i have created a from-country
and from-city tables and to-country and to-city tables which have
identical information. (Which i feel is not correct)... require advice
on this...

The selected from-destination and to-destination would be used as a
parameter for a query.

Any help Pls, as i am bit new to Access.

[Bob Quintal]

"praz" <ge*****@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

I need to have a combo box which will display the countries
from(Country table) and then user would select the country,
which would display the cites for that particular country, in
another combo box.

I have set the relation from the country table to the city
table. The problem is when I select a country from the country
combo box the relevant cities are not displayed on the city
combo box. I assume since the relationship is set as i select
a country from the country combo box the information in the
city combo box would change......like the way it happens in
form and subforms....... that's my understanding i might be
wrong. Anyway i tried this it does not work.

Then i tried to change the control source for the city combo,
where it is a query which would pull up the cities for the
selected country in the country combo box.... this also
doesn't seems to work

Actually what i need to do is, the user to select a origin
country/city and a destination country/city. For this i have
created a from-country and from-city tables and to-country and
to-city tables which have identical information. (Which i feel
is not correct)... require advice on this...

The selected from-destination and to-destination would be used
as a parameter for a query.

Any help Pls, as i am bit new to Access.

You only need one country-city table and one country table, as
you suspect. The rowsource for both country comboboxes refers to
the same table. For the rowsource of each city, use a slightly
different query, just make the where clause of the query point to
the relevant country combobox.

In each of your country comboboxes,you will want the bound column
of the combobox to be the countryID field, and the second field
the country name, and the displaywidth of the countryid 0.
Now assuming your from country box is named cbo_Country_from,
create the cbo_city_from box. Its rowsource should be

SELECT cityid, city from cities where countryID = forms!formname!
cbo_country_from.value

In the afterupdate event of cbo_country_from, add the statement

me.cbo_city_from requery.

This is required because as you have noted, the combobox does not
force relationships. There are a lot of combobox uses that don't
want referential integrity, so you have to program it where you
do want it.

do the same thing to your cbo_city_to and cbo_country_to pairs.
--
Bob Quintal

PA is y I've altered my email address.

[praz]

Thanks a million Bob.This works!

Now I can proceed further........
Praz

[praz]

As mentioned earlier.......your solution worked for the earlier problem
i had. Now I have problem in fetching data after the user selects the
from_country/city and to_country/City....what needs to happen is a
results should be displayed from the From_country_requirement table and
To_country_Requirement table
Fields in the From_country_requirement table is as follows
Country_code
City_Code
From_Requirement_details

and the fields in the To_country_requirement table is
Country_code
City_Code
To_Requirement_details

I have a button, when clicked after the user selects the country and
cites, should display the relavent from_country requiremnet details and
to_country requirement details

Also i not able to set up relations between the country, city and
From_country_requirement table/To_country_requirement table and design
the screen to input the requirements details for a particular country
and city....... I have just populated the requirement tables manually
and trying out the query.......

Any help appreciated.....

[Bob Quintal]

"praz" <ge*****@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

As mentioned earlier.......your solution worked for the
earlier problem i had. Now I have problem in fetching data
after the user selects the from_country/city and
to_country/City....what needs to happen is a results should be
displayed from the From_country_requirement table and
To_country_Requirement table Fields in the
From_country_requirement table is as follows Country_code
City_Code
From_Requirement_details

How do you intend to display the data(in a subform, a popup form,
a control on the existing form)?

Let's say you intend to show a new form. The button should
execute the following statements:

Docmd.openform "frm_From_couhntry_requirement",,"Country_Code =
" & me.cbo_country_from & " AND City_Code = " & me.cbo_City_From

And a similar one for the second form.

If you instead want to populate a textbox on the already open
form, Use
me.txt_FromCountryRequirements = DLookup
("From_Requirement_Details", From_country_requirement",
"Country_Code = " & me.cbo_country_from & " AND City_Code = " &
me.cbo_City_From)

It's really difficult to understand exactly what you want.
and the fields in the To_country_requirement table is
Country_code
City_Code
To_Requirement_details

I have a button, when clicked after the user selects the
country and cites, should display the relavent from_country
requiremnet details and to_country requirement details

Also i not able to set up relations between the country, city
and From_country_requirement table/To_country_requirement
table and design the screen to input the requirements details
for a particular country and city....... I have just populated
the requirement tables manually and trying out the
query.......
I don't know why you have trouble with this. Tell us error
messages, misbehaviour

Any help appreciated.....

--
Bob Quintal

PA is y I've altered my email address.

[praz]

Hi Bob,

I am trying to display the data in a new form and have put these
statements (tried both them) on the button's on click property....

DoCmd.OpenForm "from_details subform1", , , "[Country_Code] = " &
Forms![From_CC_To_CC]!Combo0.Value & " AND [City_Code]= " &
Forms![From_CC_To_CC]!Combo4.Value
'" & me.cbo_country_from & " AND City_Code = " & me.cbo_City_From
DoCmd.OpenForm "from_details subform1", , , "[Country_Code] = " &
Me.Combo0 & " AND [City_Code]= " & Me.Combo4

When both these statements are executed ....it gives a Enter parameter
value box with my correct choices just above the text box within the
Enter parameter value box....... and then when i input my Country_code
and then City_code it open the new form with the correct data......

How can I avoid the Enter parameter value box?

Also when i don't select any country and city........and then click on
the button......it gives me an Run time error 3075

Syntax Error (missing operator) in query expression '[country_code] =
AND [City_code]='

Pls advice...... Thanks.