populate

[Guest]

Fisrt timer I am new to access

I have a database that I want to make better.
In a form I have the following fields:

Country, State, City

I have 3 tables named

1 Country = CountryID - Country
2 State = StateID – State –Country (foreign key country) 1 to many country
to state
3 City = CityId – city- state (foreign key state) 1 to many state to city

When I select the country (USA) I want the states to appear
When I select the state I want the cities to appear

Please and thanks

 

[Steve Schapel]

Ian,

There is no purpose served by having a CountryID field in the Country
table. As far as I know, there are no two countries with the same name.
I would just have the one field Country in the Country table.

On the other hand, there may well be states named the same in more than
one country, so I guess the StateID in the State table is legitimate.
But there is a problem here with countries that don't have
states/provinces/whatever, as in this case your schema wouldn't allow
for the Cities to be listed.

In any case, it would be StateID and not State which should be the
foreign key in the City table.

Just a hint about terminology... forms don't have fields. Forms have
controls (textboxes, comboboxes, etc, etc), some of which can be bound
to fields.

Not a complete answer to your question, but hopefully of some help in
getting your database organised.

 

[Guest]

Thanks steve i have made the changes in the daabase where should i post to
get some assistance

I am new to this

I have a database that I want to make better.
In a form I have the following comboboxes:

Country, State, City

I have 3 tables named

1 Country = CountryID - Country
2 State = StateID – State –CountryID (foreign key country) 1 to many country
to state
3 City = CityId – city- stateID (foreign key state) 1 to many state to city

When I select the country (USA) I want the states to appear
When I select the state I want the cities to appear

Please and thanks

 

[Steve Schapel]

Ian,

Good to see progress so far... although I note you did not take my
previous recommendation to dispense with the CountryID field.

Ok, next step is that your comboboxes should not be Country, State,
City. They should be CountryID (if you choose to persist with this idea
of having a CountryID field), StateID, and CityID. You can set the
comboboxes up so that it is the State, City, etc values that you see,
but it is the ID values that they work with. For example, you can set
these properties of the StateID combobox:
Column Count: 2
Bound Column: 1
Column Widths: 0;x (where x is however wide you need it to be to show
the widest state name)

Then, here's an example: Make a query based on the City table, and in
the Criteria of the StateID column put the equivalent of...
[Forms]![NameOfYourForm]![StateID]
Then, make this query the Row Source of the CityID combobox.
Then, on the After Update event property of the StateID combobox, enter
"Event Procedure", click the little ellipsis (...) button to the right
of the property box, which will open the VB Editor window, and in the
space between the Private Sub and End Sub lines, type this...
Me.CityID.Requery

After this, what should happen is that when you select a State in the
StateID combobox, the CityID combobox should only list cities in the
selected state. If you want the cities list to be immediately
displayed, expand the code like this...
With Me.CityID
.Requery
.SetFocus
.DropDown
End With

 

[Guest]

Hi steve sorry i did change the country to country my error

Ian,

Good to see progress so far... although I note you did not take my
previous recommendation to dispense with the CountryID field.

Ok, next step is that your comboboxes should not be Country, State,
City. They should be CountryID (if you choose to persist with this idea
of having a CountryID field), StateID, and CityID. You can set the
comboboxes up so that it is the State, City, etc values that you see,
but it is the ID values that they work with. For example, you can set
these properties of the StateID combobox:
Column Count: 2
Bound Column: 1
Column Widths: 0;x (where x is however wide you need it to be to show
the widest state name)

Then, here's an example: Make a query based on the City table, and in
the Criteria of the StateID column put the equivalent of...
[Forms]![NameOfYourForm]![StateID]
Then, make this query the Row Source of the CityID combobox.
Then, on the After Update event property of the StateID combobox, enter
"Event Procedure", click the little ellipsis (...) button to the right
of the property box, which will open the VB Editor window, and in the
space between the Private Sub and End Sub lines, type this...
Me.CityID.Requery

After this, what should happen is that when you select a State in the
StateID combobox, the CityID combobox should only list cities in the
selected state. If you want the cities list to be immediately
displayed, expand the code like this...
With Me.CityID
.Requery
.SetFocus
.DropDown
End With

--
Steve Schapel, Microsoft Access MVP

Thanks steve i have made the changes in the daabase where should i post to
get some assistance

I am new to this

I have a database that I want to make better.
In a form I have the following comboboxes:

Country, State, City

I have 3 tables named

1 Country = CountryID - Country
2 State = StateID – State –CountryID (foreign key country) 1 to many country
to state
3 City = CityId – city- stateID (foreign key state) 1 to many state to city

When I select the country (USA) I want the states to appear
When I select the state I want the cities to appear

Please and thanks

 

[Guest]

Hi steve, I did change the country to country, created a new datadase to help
make it easier for me to understand

The tables are:

Country = Country
State = StateID, State, Country (1 Country to many State)
City = CityID, City, StateID (I State to many City)

I created a new form named “customersâ€
This form has the following controls
CompanID = auto number
Company name = text

Row source type Row source
Country Table/Query SELECT Country.Country FROM Country;
State Table/Query SELECT State.StateID FROM State;
City Table/Query SELECT City.CityID FROM City;

I am lost when doing the query, I my data basewindow I select query and used
the wizard and selected the fields in the city table, then I selected the sql
statement SELECT City.CityID, City.City, City.StateID
FROM City; ( from there I am completely lost)

Ian,

Good to see progress so far... although I note you did not take my
previous recommendation to dispense with the CountryID field.

Ok, next step is that your comboboxes should not be Country, State,
City. They should be CountryID (if you choose to persist with this idea
of having a CountryID field), StateID, and CityID. You can set the
comboboxes up so that it is the State, City, etc values that you see,
but it is the ID values that they work with. For example, you can set
these properties of the StateID combobox:
Column Count: 2
Bound Column: 1
Column Widths: 0;x (where x is however wide you need it to be to show
the widest state name)

Then, here's an example: Make a query based on the City table, and in
the Criteria of the StateID column put the equivalent of...
[Forms]![NameOfYourForm]![StateID]
Then, make this query the Row Source of the CityID combobox.
Then, on the After Update event property of the StateID combobox, enter
"Event Procedure", click the little ellipsis (...) button to the right
of the property box, which will open the VB Editor window, and in the
space between the Private Sub and End Sub lines, type this...
Me.CityID.Requery

After this, what should happen is that when you select a State in the
StateID combobox, the CityID combobox should only list cities in the
selected state. If you want the cities list to be immediately
displayed, expand the code like this...
With Me.CityID
.Requery
.SetFocus
.DropDown
End With

--
Steve Schapel, Microsoft Access MVP

Thanks steve i have made the changes in the daabase where should i post to
get some assistance

I am new to this

I have a database that I want to make better.
In a form I have the following comboboxes:

Country, State, City

I have 3 tables named

1 Country = CountryID - Country
2 State = StateID – State –CountryID (foreign key country) 1 to many country
to state
3 City = CityId – city- stateID (foreign key state) 1 to many state to city

When I select the country (USA) I want the states to appear
When I select the state I want the cities to appear

Please and thanks

 

[Steve Schapel]

Ian,

Just enter the names of the tables or queries as the Row Source for the
comboboxes. The way you have dine it, you won't be able to adjust the
combobox properties as I suggested previously, and see the name of the
state on the form.

As for creating the query to be used for the CityD combobox's row
source, as per my earlier example, I would recommend using the query
designer. In Access, I would generally advise to avoid wizards, there
are very few of them that help. Select the Queries tab in the database
window, click New, select Design View, click OK, select the City table
in the Show Table dialog, click Add, then click Close. Drag all 3
fields from the table and drop them into the query design grid. In the
Criteria row of the StateID column, type [Forms]![Customers]![StateID],
close the query, save and name it. Then enter the name of this query in
the Row Source property of the CityID combobox.

Do an equivalent process to set up a query for the Row Source of the
StateID combobox, using the Country as the criteria.

 

[Guest]

Hi steve i am now totally lost thank for you help

Ian,

Just enter the names of the tables or queries as the Row Source for the
comboboxes. The way you have dine it, you won't be able to adjust the
combobox properties as I suggested previously, and see the name of the
state on the form.

As for creating the query to be used for the CityD combobox's row
source, as per my earlier example, I would recommend using the query
designer. In Access, I would generally advise to avoid wizards, there
are very few of them that help. Select the Queries tab in the database
window, click New, select Design View, click OK, select the City table
in the Show Table dialog, click Add, then click Close. Drag all 3
fields from the table and drop them into the query design grid. In the
Criteria row of the StateID column, type [Forms]![Customers]![StateID],
close the query, save and name it. Then enter the name of this query in
the Row Source property of the CityID combobox.

Do an equivalent process to set up a query for the Row Source of the
StateID combobox, using the Country as the criteria.

--
Steve Schapel, Microsoft Access MVP

Hi steve, I did change the country to country, created a new datadase to help
make it easier for me to understand

The tables are:

Country = Country
State = StateID, State, Country (1 Country to many State)
City = CityID, City, StateID (I State to many City)

I created a new form named “customersâ€
This form has the following controls
CompanID = auto number
Company name = text

Row source type Row source
Country Table/Query SELECT Country.Country FROM Country;
State Table/Query SELECT State.StateID FROM State;
City Table/Query SELECT City.CityID FROM City;

I am lost when doing the query, I my data basewindow I select query and used
the wizard and selected the fields in the city table, then I selected the sql
statement SELECT City.CityID, City.City, City.StateID
FROM City; ( from there I am completely lost)

 

[Steve Schapel]

Ian,

I realise it is difficult when you try to do something new for the first
time. However, this procedure is actually very simple, and I have tried
to give step-by-step instructions, so I am not sure where the problem
lies. Did you actually try to do what I described in this? (which I now
break into separate steps):
1. Select the Queries tab in the database window
2. Click New
3. Select Design View
4. Click OK
5. Select the City table in the Show Table dialog
6. Click Add, then click Close
7. Drag all 3 fields from the table and drop them into the query design
grid
8. In the Criteria row of the StateID column, type
[Forms]![Customers]![StateID]
9. Close the query, save and name it
10. Enter the name of this query in the Row Source property of the
CityID combobox

After this, as per my earlier reply...
11. Set these properties of the CityID combobox:
Column Count: 2
Bound Column: 1
Column Widths: 0;x (where x is however wide you need it to be to show
the widest city name)
12. Go to the After Update event property of the StateID combobox
13. Enter "Event Procedure"
14. Click the little ellipsis (...) button to the right of the property
box, which will open the VB Editor window
15. In the space between the Private Sub and End Sub lines, type this...
With Me.CityID
.Requery
.SetFocus
.DropDown
End With

If there is anything specific in these instructions that you don't
understand, please let me know, and I will try to explain better.