Country / City combo box

[dana71]

Hi
My name is Dana and this is my first post here .
I am a DB newbie , i started to teach my self MS Access with a small project as a start .
I made a simple client data table where there are country and city fields + phone , fax , etc etc ... .
I made 2 tables 1 for the countries and 1 for the cities .
I know that it is a very familiar question
How can we do the following
when you select a country in the client table , only cities of this country show up in the city combo box .
I found some answers here and there , but as a newbie , the codes where difficult to understand .
I attached my source file and i will be thankful if someone could help and to give me a simple explanation of the codes concept .
I just want to add the codes in the table not in a form , i found some answers by adding codes to a Form . Because as i know i can export only tables and queries to sql.

 

[boblarson]

I redesigned it slightly for you so it was actually normalized. You shouldn't be storing the country name in two places.

What you need is to have the Countries table have the country names and ID's (which you did), but in the Cities table you store the country ID and not the name. Then, in the customer table you don't need the country as it is tied to the city ID.

I think you'll see. Plus I put in the After Update of the combo box (and On Current event of the form) the code to put in the country name in the text box that is displayed on the customer form, but it isn't stored. It is just in an unbound text box for display purposes.

 

[dana71]

Thank you very much for your reply.

i have 2 questions if you please :
1- i noticed that the code is added to the form and not inside the client table , can we add this code inside client table , to the country combo box ?
Why ? because i want to export this project in the end to mysql and as i know - and may be it is wrong - that i can only export tables and queries .
i want to make this concept work if i used these tables in mysql and contect to it by php.
so can we put the sql code in the client table ?
2- i think that i didn't explain what i want exactly , when we are in the client table i choose country form the country combo box field , and once i choose the country only cities of this country appear in the cities combo box ( also inside the client table ) .

Can you help me please in this point ?

Thank you for your time .

Dana

 

[boblarson]

Thank you very much for your reply.

i have 2 questions if you please :
1- i noticed that the code is added to the form and not inside the client table , can we add this code inside client table , to the country combo box ?
Why ? because i want to export this project in the end to mysql and as i know - and may be it is wrong - that i can only export tables and queries .
i want to make this concept work if i used these tables in mysql and contect to it by php.

You don't need it in the client table. It is already in the Cities table and you can create a multi table query to pull everything together the way you want it when you export. I'm assuming from your response that you are used to only using one table inside a query. I'll put a sample query in the database.

2- i think that i didn't explain what i want exactly , when we are in the client table i choose country form the country combo box field , and once i choose the country only cities of this country appear in the cities combo box ( also inside the client table ) .

Yeah, that didn't come across, but I'll modify it slightly to accomplish it.

 

[dana71]

Thank you very much Bob

Well i need your advice in something , my main goal is to learn mysql and php to create dynamic website for a specific project .

So i thought that starting with MS access and to understand how to create tables and the relations and how to create queries , then to continue to sql with a minimum basics of codes and commands which could help me to understand sql , then to mysql then to php so i can connect to my db and display it in a dynamic web site .

i know it looks long turn because when i started to learn web design i started with html as codes then dreamweaver , and i still HTML hand coder .

i tried this time to reverse it , starting with GUI program to see how it works then to learn the codes .

What do you think ? any advice ?

Thank you

 

[boblarson]

Personally, I think it might be useful to you to start with Access, but you should also read up on Database Normalization and design as those are two keys to getting anything done correctly on any relational database management system (RDBMS).

 

[dana71]

Personally, I think it might be useful to you to start with Access, but you should also read up on Database Normalization and design as those are two keys to getting anything done correctly on any relational database management system (RDBMS).

So i can say that i am on the right track .
Any recommendations about Database Normalization and design , PDF or sites
And in which stage i should move up to sql , i mean what do i need to know more than tables , relations and query to make a good base for the sql ?
Could you please modify my test combo file so i can learn from it how i can make the combo box ( cities related to countries )
Thank you again .

 

[boblarson]

Any recommendations about Database Normalization and design , PDF or sites

Not at the moment, but I'll check later.

And in which stage i should move up to sql , i mean what do i need to know more than tables , relations and query to make a good base for the sql ?

That's a hard question to answer because it really all depends on how you feel you have learned the stuff. You can go straight there if you wanted, but Access is just easier in quickly creating and testing things than working in SQL Server or MySQL but it's all pretty much the same (there are, of course, differences but those distinctions can come later).

Could you please modify my test combo file so i can learn from it how i can make the combo box ( cities related to countries )

Yes, I will be working on it in between things here at work (I am at work, so unfortunately that has to come first )