autofill form

[vibee]

i basically have an address database, consisting of name, city, province, and country. The form has these fields as well, name, city, province, and country. Everything is fed into one table. I want to know if there is a way to autofill, so that if the user were to enter the city as toronto, the province would automatically appear as ontario. im pretty sure the answer to this question has already been posted in a different way, but i just cant find it. please help me out thanks!

[FishVal]

i basically have an address database, consisting of name, city, province, and country. The form has these fields as well, name, city, province, and country. Everything is fed into one table. I want to know if there is a way to autofill, so that if the user were to enter the city as toronto, the province would automatically appear as ontario. im pretty sure the answer to this question has already been posted in a different way, but i just cant find it. please help me out thanks!

Hi.

The following code will do it. Certainly the names of form controls, table fields and table itself may be different in your db.

 
Private Sub txtCity_AfterUpdate()
	Me.txtProvince = DLookup("txtProvince", "tblAddresses", _
		"txtCity='" & Me.txtCity & "'")
	Me.txtCountry = DLookup("txtCountry", "tblAddresses", _
		"txtCity='" & Me.txtCity & "'")
End Sub
 
Private Sub txtProvince_AfterUpdate()
	Me.txtCountry = DLookup("txtCountry", "tblAddresses", _
		"txtProvince='" & Me.txtProvince & "'")
End Sub

But it should be born in mind that you have completely unnormalized db. The same data (city, province, country names) is stored in multiple places. In this case extra db space consuming is only a minor or even negligible disadvantage, the main one is multiple copies of the same data, I hope you are aware about what it will cause. I recommend you to read an article about db normalization
Database Normalisation and Table structures

Good luck.

[vibee]

i dont want to look up through the form, i just sort of want it as an if statement even though i know its easier to do a look up... for example i was thinking of the lines along

if txtcity = toronto then txtprovince = ontario

is it possible to do it like that?

[FishVal]

i dont want to look up through the form, i just sort of want it as an if statement even though i know its easier to do a look up... for example i was thinking of the lines along

if txtcity = toronto then txtprovince = ontario

is it possible to do it like that?

How many if...then statements do you want to add to your code ?
Would you like to add new if...then statement each time new city is added?
What is wrong with table lookup? :) I can only guess what a special reason you have to avoid using of Access benefits. :))

[NeoPa]

But it should be born in mind that you have a completely unnormalized db. The same data (city, province, country names) is stored in multiple places. In this case extra db space consuming is only a minor or even negligible disadvantage, the main one is multiple copies of the same data, I hope you are aware about what it will cause. I recommend you to read an article about db normalization
Database Normalisation and Table structures

Good luck.

I would put it far more strongly :
Don't even think about doing it this way without normalisation.
As Fish has strongly hinted - you will only regret it.

[vibee]

what are the possible regerts because i really have no idea even after reading the article

[NeoPa]

What are the possible regrets? Because I really have no idea, even after reading the article.

The main regret will be that everything you try to do with it will be many times more complicated.
Almost as bad is that few members will want to help you to work in a way that they find unnatural. Aesthetically it's 'uncomfortable' for someone who works with databases even to think in that way.
Lastly, communication is likely to be a struggle if you have trouble with the concepts of normalisation.

[vibee]

hmm alright thanks for the advice...hence attempting to normailze begins..sigh... wish i had done it sooner

[NeoPa]

We can help you to understand the concepts.
We can give advice if you have questions as to the best way to do or design something.
Good luck :)

[vibee]

ok ive tried normailzing by using the analyze table method, but i still dont clearly see the purpose of it any way of dumbing it down further?:S thanks, sure it splits it into different tables, but i can do that with queries no?

[Scott Price]

My 2 cents worth :-)

Database normalization is a rather arcane topic that you will not be able to really understand until you work your way a little further into the database design process.

Simply put the rules of db design were developed years ago by some mathematicians working in things like 3rd order predicate logic, etc. Access and other db programs are built with these rules in mind. Yes, you can fudge your way around the way these rules/programs are built, but you do so at considerable cost to your brainpower, frustration levels, etc... Some things also not only become much more difficult to do, they can become downright impossible! A much simpler (and far more productive and safe) method is to simply accept that the rules are there for a reason.

As you work on db's more, you will begin to learn WHY the rules are there.

One simple example using your scenario: Suppose you store everything in one big table. Then next year, Quebec decides it doesn't want to be part of Canada anymore (yeah, sure... never happen...). How are you going to change the values in your 'one big table' that refer to Quebec? You will have to write code to search the fields to find each instance of the word Quebec and change it to whatever it gets changed to.

If, instead, you have a normalized db, the change is quite simple: you go to the table named something like tblProvinces, and make one change to the entry Quebec which then affects every entry that it is related to through table relationships. This concept is called data integrity, and is far more important than this simple example illustrates...

Change the example to involve accounting systems and $'s and you will understand perhaps more of the potential errors involved.

For example if you are storing employee salaries in one big table that has all information in it. How are you going to find salaried employees of a certain level of salary? Write code to search the fields to find the instances between a certain $ figure... How are you then going to change each employees salary when they get a raise? How are you going to generate a report to make sure that someone isn't (horrors) making more money than their experience/skill level?

It's only possible to dumb this subject down to a certain point! If you weren't willing to learn how to do it right, you wouldn't be here at this site asking for pointers, so I hope the above information is some help!

Scott

[NeoPa]

This is such an excellent post that I will add it (assuming you have no objection) to the Normalisation thread itself when I get a chance. There will need to be minor edits to strip out the bits that are particular to this thread, but otherwise this will make a very helpful addition to the article itself. Thank you for this Scott :)

[Scott Price]

You're welcome, NeoPa, and feel free to use it! Glad it could be useful!

[NeoPa]

You can check it out (Database Normalisation and Table structures).

[vibee]

now that makes sense scott, thank you very much for the explanation