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”

  1. My programmer is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the costs. But he’s tryiong none the less. I’ve been using WordPress on various websites for about a year and am concerned about switching to another platform. I have heard very good things about blogengine.net. Is there a way I can transfer all my wordpress content into it? Any kind of help would be greatly appreciated!

  2. What’s the interest rank by this account? Insufficiently Preteen Nymphets
    A few time back I saw this guy in the news saying that he has AIDS and he already infected people.

  4. I do аccept aѕ true with all the сonсeρts
    yοu have introduced on уour post.

    They’re very convincing and will certainly work. Nonetheless, the posts are very quick for starters. May just you please extend them a little from next time? Thanks for the post.

    Visit my website Tattoo Me Now

  6. Sorry for the huge review, but I’m really loving the new Zune, and hope this, as well as the excellent reviews some other people have written, will help you decide if it’s the right choice for you.

  7. An intriguing discussion is definitely worth comment. I believe that you need to publish more about this issue, it might not be a taboo matter but usually folks don’t speak about these issues. To the next! Kind regards!!

  10. Most of my game lover good friends love to perform games, and the majority known as Monopoly the truth is just about all unblocked games with the earth. Other conventional board games of which ranked high bundled Scrabble and Threat.

  11. Remarkable things here. I am very happy to see your article. Thanks so much and I am having a look forward to touch you. Will you please drop me a e-mail?

  14. Aw, this was a really nice post. In idea I would like to put in writing like this additionally – taking time and actual effort to make a very good article… but what can I say… I procrastinate alot and by no means seem to get something done.

  15. Thank you for any other magnificent post. Where else could anybody get that kind of information in such a perfect means of writing? I’ve a presentation subsequent week, and I’m on the search for such information.

  16. What i ԁon’t realize is in truth how you are not really a lot more smartly-liked than you might be right now. You are very intelligent. You know thus considerably on the subject of this topic, produced me personally consider it from so many various angles. Its like men and women don’t seem to be fascіnateԁ except it is one thing to accomplish
    with Girl gaga! Your οwn ѕtuffs nісe.

    All the tіme handlе it uр!

  18. Great blog! Is your theme custom made or did you download it from somewhere? A design like yours with a few simple tweeks would really make my blog shine. Please let me know where you got your theme. Many thanks

  20. These are cell phone browser games likewise, which inturn needs no complicated get to learn. All you have to is usually your relatively fast world-wide-web relationship and will also be equipped to appreciate them to your own personal center information.

  21. Nice read, I just passed this onto a friend who was doing a little research on that. And he just bought me lunch as I found it for him smile So let me rephrase that: Thank you for lunch!

  25. Simply wish to say your article is as astonishing. The clarity in your post is simply great and i could assume you are an expert on this subject. Well with your permission let me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please keep up the rewarding work.

  27. Magnificent goods from you, man. I’ve understand your stuff previous to and you are just extremely fantastic. I really like what you have acquired here, certainly like what you’re stating and the way in which you say it.
    You make it entertaining and you still take care of to keep it sensible.
    I can’t wait to read far more from you. This is actually a tremendous site.

    My site: apple cider vinegar diet

  28. Place a silpat liner on a baking sheet (or spray with nonstick cooking spray).
    Just be sure to sprinkle the bottom with cornmeal so the pizza
    will slide easier and pull the oven rack out
    so you aren’t likely to get injured while sliding it onto the pizza stone. It is not like ordinary dough, it is not supposed to be soft as a baby’s bottom.

  29. I loved aѕ much as уou ωill recеivе carried
    out rіght herе. Τhe sketch is tasteful, youг authοred ѕubject
    matter stylish. nonetheless, you command get bought an edginess оѵеr that you wiѕh be delivering thе
    following. unwеll unquestionablу come more
    formегly agаin aѕ exactly the same nеaгly a lot
    οften inside сase you shield this increase.

    my weblοg – Primal Burn review

  30. When an individual decides to make the transition from eating meat to being a
    true vegan, many of the usual things they once enjoyed
    go out the window. Never try to remove a pizza stone from the
    oven until it is completely cooled. It didn’t help that every pizza recipe I saw online would mention the dreaded “bread machine.

  31. I¡¦ve been exploring for a bit for any high-quality articles or blog posts on this sort of space . Exploring in Yahoo I eventually stumbled upon this website. Studying this info So i¡¦m happy to exhibit that I’ve an incredibly just right uncanny feeling I found out exactly what I needed. I such a lot certainly will make sure to don¡¦t overlook this web site and provides it a look regularly.

  33. Throughout the awe-inspiring end of things you secure an A+ with regard to unbreakable do. Somewhere you actually absorbed everyone was by all the particulars. You get, as the maxim goes, the devil is in the details… As well as it could not be real a large amount added correct at this point. Having said that, okay me reveal to you just what did create good outcome. Your writing is definitely same powerful and this is maybe why I am making an effort in array to comment. I perform not achieve it a repeated habit of doing that. 2nd, even if I can undoubtedly observe the jumps in incentive you make, I am not truly persuaded of just how you appear to connect your points that help to compose your irrevocable conclusion. On behalf of acceptably at this moment I will yield to your point nonetheless custody in the almost impending you actually link up the facts much better.

  34. Thаnk you for another іnformative sitе.

    The ρlaсe elѕe сould I get that tyрe of information written in such an
    ideal manner? I’ve a challenge that I’m simply noω opеrating on, and I’ve been on the glance out for such information.

    Feel free to surf to my web page: My Shed Plans scam

  35. Simple and Easy to Create Recipes with clear step by step instructions you
    will be able to produce paleo friendly meals that get rave reviews from friends and family every- time.
    Obesity is an epidemic in our society. Always check your physician before making any changes
    to your daily habits.

  38. Thanks a bunch for sharing this with all folks you actually recognize what you are talking about! Bookmarked. Kindly additionally visit my website =). We may have a hyperlink change agreement among us

  39. Hands down, Apple’s app hoard wins in advance of a mile. It’s a jumbo movement of all sorts of apps vs a quite ineffective preference of a some pro Zune. Microsoft has plans, remarkably in the charge of games, but I’m not satisfied I’d shortfall to endanger on the following if this point of view is first to you. The iPod is a much safer exquisite in that case.

  40. Hurrah, what a quality it is! For the reason that mostly YouTube video tutorials have no pleasant quality, except this is truly a pleasant quality video.

  42. My neighbor and I have been just debating this particular topic, he’s often seeking to show me incorrect. Your view on this is great and exactly how I really feel. I simply now mailed him this website to show him your personal view. After wanting over your website I e book marked and will be coming again to read your new posts!

  43. hi!,I really like your writing so much! percentage we be in contact extra approximately your article on AOL? I require a specialist on this space to unravel my problem. May be that is you! Taking a look ahead to look you.

  44. If you’re still on the fence: grab your favorite earphones, head down to a Best Buy and ask to plug them into a Zune then an iPod and see which one sounds better to you, and which interface makes you smile more. Then you’ll know which is right for you.

  45. Gym floor washing is usually some sort of very difficult process primarily during locations where furthermore there is big site visitors prefer high schools, establishments and so forth

  46. A new thouroughly tested procedure designed for carpet cleaning nyc is definitely just by implementing pickup truck affixed carpet cleaning tools.

  47. Encapsulation rugs cleansing agents are as well really favorite during classes because they are some more reliable, necessitate not as drying out as well as are generally efficient while they waste matter fewer normal water like when compared so that you can various other systems.

  49. I comment when I especially enjoy a post on a blog or I have something to contribute to the discussion. Usually it’s caused by the fire communicated in the article I looked at. And on this post How to auto fill fields in access (2010) based on combo box selection | Shane Zentz's Blog. I was moved enough to drop a thought 😉 I do have 2 questions for you if it’s okay. Is it simply me or does it look like like some of the comments come across like left by brain dead people? 😛 And, if you are posting on additional sites, I would like to keep up with everything fresh you have to post. Could you make a list every one of all your community sites like your linkedin profile, Facebook page or twitter feed?

  50. Encapsulation new carpet purifiers are moreover fairly widely used on school choices because they are further more successful, necessitate much less drying out some can be affordable as they simply waste matter fewer liquid since when compared to that will different options.

Comments are closed.