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!
14 2204
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.
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?
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 32,556
Expert Mod 16PB
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.
what are the possible regerts because i really have no idea even after reading the article
NeoPa 32,556
Expert Mod 16PB
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.
hmm alright thanks for the advice...hence attempting to normailze begins..sigh...wish i had done it sooner
NeoPa 32,556
Expert Mod 16PB
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 :)
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?
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 32,556
Expert Mod 16PB
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 :)
You're welcome, NeoPa, and feel free to use it! Glad it could be useful!
NeoPa 32,556
Expert Mod 16PB
now that makes sense scott, thank you very much for the explanation
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Chris Sharman |
last post by:
I'd like to design my pages to work cooperatively with browser autofill
features.
I've loked around, but can't find any good documentation on
supported/unsupported field names...
|
by: shortbackandsides.no |
last post by:
I'm having a lot of difficulty trying to persuade the Google toolbar
autofill to act consistently, for example
=======================
<html><head>
<title>autofill test</title>
</head><body>...
|
by: David Portabella |
last post by:
Hello,
I am a doing a survey about Autofill Web Forms softwares.
Usually, they all collect information of the user once during the
set-up phase (user knowledge base).
Then, when the user...
|
by: Ray Holtz |
last post by:
Is it possible to autofill a field based on what is entered into another
field in a form? My form has an employee field, and department field.
In an Items Table, I have fields FldEmployee, and...
|
by: Nick J |
last post by:
Hi,
I remember at one point access would autofill a text box when filtering by
form, Like for example as I would type it would come up with matching
records, similar to AutoComplete in Internet...
|
by: HTS |
last post by:
I have written a membership database application (PHP + mySQL) and need to prevent autofill from filling in fields in a member record edit form.
I know I can turn off autoFill in my browsers, I...
|
by: Randy |
last post by:
Hi,
I have some comboboxes that are created dynamically at run time based
on user actions. I found a procedure on a message board to autofill
the combobox text from the dataview that the...
|
by: berlich |
last post by:
Can I use visual basic.net to access the DOM in a web page so that I can autofill the text boxes? If so, can you point me in the right direction as to where to begin?
I need to fill in a lengthy...
|
by: rsmccli |
last post by:
Access 2002
Hi, I have a number of comboboxes/textboxes in the header of a form that the user selects values from. These comboboxes/textboxes correspond to controls in a datasheet view subform....
|
by: Nik Coughlin |
last post by:
I'm doing some ajax validation on form fields, using the form change event.
If I click Autofill in the Google Toolbar, fields are filled out but the
change event is never fired, so the validation...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |