473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

autofill form

27
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!
Jun 29 '07 #1
14 2204
FishVal
2,653 Expert 2GB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub txtCity_AfterUpdate()
  3.     Me.txtProvince = DLookup("txtProvince", "tblAddresses", _
  4.         "txtCity='" & Me.txtCity & "'")
  5.     Me.txtCountry = DLookup("txtCountry", "tblAddresses", _
  6.         "txtCity='" & Me.txtCity & "'")
  7. End Sub
  8.  
  9. Private Sub txtProvince_AfterUpdate()
  10.     Me.txtCountry = DLookup("txtCountry", "tblAddresses", _
  11.         "txtProvince='" & Me.txtProvince & "'")
  12. End Sub
  13.  
  14.  
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.
Jun 30 '07 #2
vibee
27
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?
Jul 1 '07 #3
FishVal
2,653 Expert 2GB
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. :))
Jul 1 '07 #4
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.
Jul 2 '07 #5
vibee
27
what are the possible regerts because i really have no idea even after reading the article
Jul 13 '07 #6
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.
Jul 13 '07 #7
vibee
27
hmm alright thanks for the advice...hence attempting to normailze begins..sigh...wish i had done it sooner
Jul 13 '07 #8
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 :)
Jul 13 '07 #9
vibee
27
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?
Jul 15 '07 #10
Scott Price
1,384 Expert 1GB
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
Jul 15 '07 #11
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 :)
Jul 16 '07 #12
Scott Price
1,384 Expert 1GB
You're welcome, NeoPa, and feel free to use it! Glad it could be useful!
Jul 16 '07 #13
NeoPa
32,556 Expert Mod 16PB
You can check it out (Database Normalisation and Table structures).
Jul 16 '07 #14
vibee
27
now that makes sense scott, thank you very much for the explanation
Jul 17 '07 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
1
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>...
0
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...
0
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...
1
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...
4
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...
0
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...
1
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...
2
rsmccli
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....
1
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
0
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...
1
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.