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. I have to show some appreciation to you just for bailing me out of such a predicament. After looking out throughout the world-wide-web and seeing proposals which were not helpful, I figured my entire life was over. Existing without the presence of answers to the issues you have resolved by way of your main post is a serious case, as well as ones which could have badly affected my entire career if I hadn’t noticed your web site. Your main training and kindness in taking care of everything was very helpful. I’m not sure what I would’ve done if I had not come upon such a solution like this. I am able to at this moment relish my future. Thanks very much for this impressive and result oriented guide. I won’t hesitate to recommend your web site to anyone who ought to have care about this subject.

  3. you’re in point of fact a good webmaster. The web site loading velocity is amazing. It seems that you’re doing any unique trick. Also, The contents are masterpiece. you have done a excellent task on this matter!

  4. Definitely imagine that that you said. Your favourite justification appeared to be at the web the easiest thing to consider of. I say to you, I definitely get annoyed whilst other folks consider worries that they just do not recognize about. You controlled to hit the nail upon the top and also defined out the entire thing without having side effect , people can take a signal. Will likely be again to get more. Thanks

  5. Fіne way of exρlainіng, and good post to takе
    іnformatіon on the tοpic of my presеntаtiοn ѕubjеct, whісh i аm goіng to
    dеliver in school.

  6. You actually make it seem really easy with your presentation but I to find this matter to be really one thing which I think I’d never understand. It sort of feels too complicated and extremely wide for me. I’m looking forward in your next publish, I’ll try to get the grasp of it!

  7. Will they be fed, watered, and cared for properly.

    This will help make navigating the weaving poles a habit for you dog.

    We turn off the TV.

  8. I simply couldn’t go away your web site before suggesting that I really enjoyed the standard info an individual supply on your visitors? Is going to be back ceaselessly in order to investigate cross-check new posts

  11. Thanks , I have recently been searching for info about this topic for ages
    and yours is the best I’ve found out so far. However, what about the conclusion? Are you positive about the supply?

  13. Hello there! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My web site looks weird when viewing from my apple iphone. I’m trying to find a theme or plugin that might be able to correct this issue. If you have any suggestions, please share. Appreciate it!

  16. Very interesting points you have remarked, thankyou for putting up. “The biggest fool may come out with a bit of sense when you least expect it.” by Eden Phillpotts.

  18. He especially enjoys one that is loaded with veggies.
    Remember to pre-heat your oven for at least 1 hour. You have
    to shake over the paddle of the openhandedly by taking use of the corn
    meal & then you have to put bread on the paddle ahead of
    mixing toppings within it.

  21. I think this is among the most vital information for me. And i’m glad reading your article. But should remark on few general things, The site style is perfect, the articles is really great : D. Good job, cheers

  22. I think that everything composed was very
    reasonable. However, think about this, what if you added a little information?
    I ain’t suggesting your content is not good, but suppose you added something to maybe grab people’s attention?
    I mean How to auto fill fields in access (2010)
    based on combo box selection | Shane Zentz’s Blog is a little boring. You should glance at Yahoo’s front page and see how
    they write article titles to get people to open the links.
    You might try adding a video or a related
    pic or two to grab people excited about everything’ve written. In my opinion, it might make your website a little livelier.

  25. I’m really impressed along with your writing abilities and also with the structure in your blog. Is that this a paid theme or did you modify it your self? Anyway keep up the nice high quality writing, it’s rare to peer a nice weblog like this one today..

  29. It is perfect time to make some plans for the long run and it is time to be happy. I’ve read this publish and if I may just I desire to suggest you few interesting issues or suggestions. Maybe you could write next articles referring to this article. I desire to read more things approximately it!

  30. It will always be a great idea when selling something to pay attention to the positive elements of the product. How can the product help people? Instead of telling people how great your merchandise is, let them know about how precisely it can help them. This technique will make the product far more fascinating to the current market you happen to be targeting. When in the business of Epxbody, it is not hard to implement this practice.

  32. I wгite a leave a response eaсh timе
    І lіke а artіcle on а blog oг Ι have something to aԁԁ to thе сonveгsation.
    Uѕually it is саuseԁ by the fіre communiсatеd in the post Ӏ
    геaԁ. And on this article Hοw to аuto fill fielԁs in accesѕ
    (2010) based on combο bοx sеlectiоn | Ѕhаne Zentz’s Blog. I was actually excited enough to drop a thought 🙂 I actually do have a few questions for you if it’s okay.

    Сoulԁ it bе just me οr dοes
    іt look aѕ if like a few оf thеse reѕpοnѕеs apρear like theу агe coming from brain ԁead іndіνiduals?
    😛 Anԁ, іf you aге posting
    on other online siteѕ, I woulԁ lіkе to
    keер up with уou. Would yοu lіѕt аll of your сommunity ρagеs like yοur lіnkeԁіn ρгοfіle, Facеbοok ρagе οr tωittег feeԁ?

  36. Undeniably believe that which you said. Your favorite reason appeared to be on the web the easiest thing to
    be aware of. I say to you, I certainly get annoyed while people think about worries that
    they plainly don’t know about. You managed to hit the nail upon the top and defined out the whole thing without having side effect , people can take a signal. Will likely be back to get more. Thanks

  42. Magnificent issues altogether, you simply received a new reader. What might you suggest about your put up that you just made some days ago? Any certain?

  46. Therefore, the mobile storage equipment safety protection at the same time, can’t effect the user use mobile storage equipment legal data storage and exchange use habit and convenience. the software is able to recover the photos from several brands of USB drives like Sandisk, Transcend, Kingston etc. The external drives that are attached to your computer and properly recognized are listed in the left pane.

    Look at my homepage; USB 8GB

  47. When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get three e-mails with the same comment. Is there any way you can remove people from that service? Thanks!

  48. What a information of un-ambiguity and preserveness of valuable know-how about unpredicted feelings.

Comments are closed.