How to auto fill fields in access (2010) based on combo box selection

Published by Shane-Zentz

How to auto fill fields in access (2010) based on combo box selection

by Shane Zentz

Access is a great program for small level databases. The kind of databases and programs that almost every small business today needs. And of course Microsoft makes it very easy for almost anyone to create a complete database driven solution. Simple order entry database programs are very common in todays business, and Microsoft Access makes it possible to create a viable solution reasonably quickly, efficiently, and economically. Access contains a number of features that make it the database application of choice for small businesses today. One of those features is the combo box. This tool allows the end-user to simply select an item from a drop down menu, instead of having to type it in manually. A real time saver. But from the programming and database designer point of view, they can sometimes be a little difficult and frustrating to work with. So this article is about what I have learned in working with these combo boxes, and how to succeed in your next Access project by using these handy tools.

For this article I will start with a simple and very basic table that looks like the following:
ID (autonumber primary key), Customer (text), Address (text), City (text), State (text), Zip Code (text). This is obviously a simple customer database which will hold typical customer data. The kind of data that a lot of small businesses would have and would need to store in an efficient and easy way. I am not going to go over how to create the tables in access as that is a little below the level of this article. But check out google for many articles on how to create basic tables and basic databases in any version of Access. So we assume that we have our customer table in place. And we can also assume that we have some arbitrary amount of (valid) data inserted into the table.

This data will be useful for many purposes to most small businesses. However in an effort to increase efficiency and also accuracy, it would be nice for the end-user of this database to be able to just search for the customers name and then all of the data related to that particular customer would then be displayed in the correct fields. This is possible with access and combo boxes. In fact there are at least two ways of doing this. The harder way and the easier way. The harder way involves adding some simple code to the combo boxes ‘after update’ property. Usually something like TextBox151=ComboBox9.Column(2). This method certainly works, but there is an even easier (and cleaner) method of accomplishing this with no code at all. So now we know what we want, we want to auto fill fields of a form or report based on the value chosen by the end user in a combo box. Read on to learn the easy way to achieve this.

Recall that we have our simple ‘Customer’ table and that it is filled with some kind of data (even test data is fine). So what we want is a form that will display that data. This is very easy in Access, but we want more. We want a combo box that will only display the customers name, and when the end user selects a certain customers name the rest of the data for that customer (address, city, state, etc.) will automatically fill the fields of the form. So here is the easy way to do this. The tricky way. First open the customer table (in this case, your table will probably be named differently) in datasheet view. With this table open, go to the ‘Create’ tab. Now on the ‘Create’ tab, you have three choices for creating a form (or possibly even more). But the first choice simply says ‘form’. This is the one that we want.

So select this choice and you will see Access will create a neat looking form that has every field of the table (Customer, in this case) that was used to create it. Now you can close out the customer table. Go to the form that Access created when you clicked on the ‘form’ button. Switch to design view. Try to make some room somewhere on the form to add the combo box. Then select the combo box tool on the ‘Design’ tab and place the combo box somewhere on this form.

 

A wizard will open. If you have added combo boxes in other places you will notice that if you use this method of adding a combo box, now you have a third option of where to get the data for the combo box from. And this new option (the bottom one) which says something like “Find a record on my form based on the value I selected from combo box”, is exactly the one that we want.

So select that and choose something logical to fill the combo box, in this case I would choose Customer (the name of the customer, to make it easy for the end user to find who they are looking for). Now save the form and switch back to form view. Test it out. You should see that when you select a customers name, then their corresponding data is automatically filled in to the other fields of the form. Like magic.

If this has not worked for you, then you have probably missed a step listed above. The most important things are to make sure that Access makes a form for you based on the table that you want to pull the data from, and that you choose the third option in the combo box wizard (the one that says something like “Find a record on my form based on the value I selected from combo box”. If these steps are followed then your form should be autofillingout itself based on the end users choice in the combo box. Now what you do with this data and this form are up to you. And remember that this is only one method of accomplishing this little bit of magic. If this does not work for you or does not suit your needs, then I would recomend trying the method which involves adding a bit of code to the ‘after update’ event on the combo box. A little more difficult, but still pretty easy. Anyway thanks for reading this Access tutorial and article.

Shane Zentz

1,166 thoughts on “How to auto fill fields in access (2010) based on combo box selection”

  2. Woah! I’m really loving the template/theme of this blog. It’s simple, yet effective.
    A lot of times it’s tough to get that “perfect balance” between superb usability and appearance. I must say you have done a fantastic job with this. In addition, the blog loads extremely quick for me on Internet explorer. Excellent Blog!

  4. Great weblog here! Additionally your site so much up fast! What web host are you using? Can I am getting your affiliate link in your host? I wish my web site loaded up as quickly as yours lol

  6. Unquestionably imagine that that you said. Your favourite reason seemed to be at the internet the easiest thing to take note of. I say to you, I definitely get irked while folks consider worries that they just don’t realize about. You managed to hit the nail upon the top and also outlined out the entire thing without having side effect , people can take a signal. Will probably be again to get more. Thank you

  9. Magnificent goods from you, man. I’ve take into accout your stuff prior to and you are simply too fantastic. I actually like what you have got here, really like what you’re stating and the way in which in which you say it. You are making it entertaining and you still care for to keep it wise. I cant wait to learn far more from you. That is actually a tremendous web site.

  10. Someone necessarily assist to make critically articles I might state. That is the first time I frequented your website page and thus far? I surprised with the analysis you made to make this actual put up extraordinary. Excellent activity!

  11. http://www.bulberryfashion2013.com gfdkzf difo burberry outlet ywjtfq zusi burberry outlet nulpta kclo http://www.specjerseys.com tehepj nlrt ugg sale jiaeyv fzhm ugg outlet store rxwvph fsve ugg boots outlet sjczti rgyo ugg boots cheap jppcbq eyvx michael kors online outlet fqekzr ulof michael kors online outlet wvahhz mwlx michael kors diaper bag kbxklt svzb longchamp on sale xipsvn preb longchamp bags on sale zohasf qetm longchamp bag fvlrwk tekv burberry outlet aqswkk ieyl

  12. http://www.bulberryfashion2013.com kaywkl idvu burberry sale shcwqf rdro burberry bags whjais omll http://www.specjerseys.com ulacyn qree http://www.numbjerseys.com plmjmj djwh http://www.2lv6.com yydddz zwtr ugg boots outlet sqmjwz snhg ugg boots cheap ldsrng fdij michael kors outlet bhyaag stww michael kors online outlet ttscvj uhdw michael kors flats rswvzt sefo longchamp outlet store knqywa szhl longchamp outlet pbshcx zbzj http://www.dtr6.com wtmcxs qpui http://www.e4ni.com acucpx hggh

  14. Hi, Neat post. There is an issue with your website in internet explorer, may test this? IE still is the marketplace leader and a large element of people will leave out your fantastic writing due to this problem.

  16. Unquestionably believe that that you stated. Your favourite justification seemed to be at the internet the easiest factor to take note of. I say to you, I definitely get irked while other folks consider worries that they plainly do not know about. You controlled to hit the nail upon the highest and also defined out the whole thing without having side-effects , other people can take a signal. Will probably be back to get more. Thanks

  17. I will immediately seize your rss as I can not to find your e-mail subscription link or newsletter service. Do you have any? Please allow me realize in order that I may just subscribe. Thanks.

  19. Hello There. I found your blog the usage of msn. This is an extremely smartly written article. I will be sure to bookmark it and come back to learn more of your useful information. Thanks for the post. I’ll certainly return.

  20. I think this is one of the most important information for me. And i am glad reading your article. But want to observation on few normal issues, The web site taste is wonderful, the articles is truly nice : D. Excellent process, cheers

  21. Magnificent items from you, man. I have understand your stuff prior to and you’re simply extremely magnificent. I really like what you’ve received right here, really like what you’re stating and the best way through which you say it. You are making it entertaining and you still care for to stay it wise. I can’t wait to read much more from you. That is actually a great site.

  22. I have been browsing online greater than 3 hours nowadays, yet I never found any fascinating article like yours. It’s beautiful value enough for me. In my opinion, if all site owners and bloggers made just right content material as you did, the internet will be a lot more useful than ever before.

  24. I simply wanted to write down a quick note in order to thank you for all the superb instructions you are sharing at this site. My extensive internet research has at the end been paid with really good facts to exchange with my company. I would say that we readers are undeniably lucky to exist in a fantastic community with many brilliant professionals with very beneficial advice. I feel truly grateful to have discovered your weblog and look forward to plenty of more awesome minutes reading here. Thanks again for everything.
    buy wow gold http://www.wow-gold-team.com/
    buy wow gold

  25. I do consider all of the ideas you have presented on your post. They’re very convincing and can certainly work. Nonetheless, the posts are too short for newbies. Could you please prolong them a bit from subsequent time? Thank you for the post.

  27. Thanks a lot for sharing this with all of us you actually recognise what you are talking approximately! Bookmarked. Please additionally discuss with my web site =). We can have a hyperlink exchange agreement between us

  28. Heya i am for the primary time here. I found this board and I to find It truly useful & it helped me out much. I am hoping to present one thing again and help others like you aided me.

  29. Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin

  31. Pingback: My Homepage

  32. This is very interesting, You’re a very skilled blogger. I have joined your feed and look forward to in quest of more of your magnificent post. Also, I have shared your site in my social networks

  33. You’re in reality a just right webmaster. The website loading velocity is amazing. It kind of feels that you are doing any unique trick. In addition, The contents are masterpiece. you’ve performed a magnificent task in this subject!

  35. Hello, i feel that i noticed you visited my website thus i got here to go back the want?.I am attempting to to find issues to enhance my site!I suppose its adequate to make use of some of your ideas!!

  36. Ιf you can furnish an woгκρlaсe spаcе
    аt hοme with the requіred IT equіpment you can
    commence sеeking on the wοrlԁ widе
    web fοг woгκ aѕ a fгeеlance
    CAD designer. Frеelance Writerѕ hаve еndleѕѕ opportunіtіes obtainablе.

    Check out my webpаgе; Milton

  37. Its such as you read my mind! You appear to grasp a lot about this, such as you wrote the e book in it or something. I believe that you can do with some percent to power the message house a bit, however instead of that, this is great blog. An excellent read. I will definitely be back.

  38. I simply could not leave your website before suggesting that I really enjoyed the usual information an individual provide to your visitors? Is gonna be back incessantly in order to check up on new posts

  41. Its like you read my mind! You appear to know so much approximately this, like you wrote the ebook in it or something. I think that you simply can do with a few p.c. to power the message home a little bit, however instead of that, that is great blog. An excellent read. I will certainly be back.

  42. I was suggested this web site by my cousin. I’m not sure whether this publish is written by way of him as no one else know such precise about my difficulty. You’re incredible! Thanks!

  46. buy priligy Cela permet a chacun de consommer cialis en fonction de leur etat probleme de. http://pe-dapoxetine.info#44602 – buy priligy Donc si vous disponibles sur un certain plusieurs qui souffre de ligne. [url=http://pe-dapoxetine.info#35111]buy priligy[/url] Il est conseille de daccorder la dose de Cialis et le mode.

  47. I’ve been exploring for a little bit for any high quality articles or blog posts on this sort of house . Exploring in Yahoo I finally stumbled upon this web site. Reading this information So i am happy to convey that I’ve a very just right uncanny feeling I found out exactly what I needed. I so much indubitably will make certain to do not fail to remember this website and give it a look on a relentless basis.

  50. Hello, i feel that i noticed you visited my weblog so i got here to go back the favor?.I’m attempting to in finding issues to improve my website!I suppose its good enough to use some of your concepts!!

Comments are closed.