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. Pingback: Flyerdruck

  2. Hi there very nice site!! Guy .. Beautiful .. Amazing .. I’ll bookmark your blog and take the feeds additionally…I am glad to search out so many helpful information right here within the submit, we’d like develop extra techniques on this regard, thanks for sharing.

  3. Long gone, I discovered your website page but I wasn’t present an excessive amount interested. And it was eventually a great mistake. Now i discovered it lower back and I see how much comes with changed! The good, of training! Congratulations and carry on the beneficial!

  4. New carpet cleanup is certainly some sort of troublesome activity certainly within areas where at this time there is substantial customers for example high schools, practices and so forth

  5. I not to mention my friends were actually reviewing the best tricks on the website and so all of a sudden I had a horrible feeling I had not expressed respect to the web site owner for those tips. The men are already so passionate to read them and have now surely been using these things. Many thanks for really being really kind and for making a decision on variety of brilliant subject matter millions of individuals are really wanting to know about. Our honest regret for not expressing appreciation to you sooner.

  6. Thanks , I’ve recently been searching for information approximately this topic for a while and yours is the best I’ve discovered so far.
    However, what about the conclusion? Are you positive
    about the supply?

    Also visit my web blog – Aquaponics 4 You pdf

  8. New carpet extractors could moreover provide to be able to eradicate fried spots this kind of since comes with, fixing herbal oils, biting mouth and so forth

  9. Rug cleanup can be the challenging job primarily within locations now there is superior website visitors enjoy academic institutions, workplaces and so forth

  10. You are required to consider not one but two elements to be able to find out the particular strength connected with carpet cleaning models.

  11. You need to do not forget a pair of issues so that you can figure out the exact usefulness regarding carpet cleaning models.

  12. t get too much direct heat during the cooking process.

    Remember to pre-heat your oven for at least 1 hour. Although the creation of flat bread
    with toppings resembles a green-onion pancake, it remains a mystery still whether the history of pizza starts with
    China.

  13. During school choices this old fashioned system for cleaning nice carpets along with your container regarding very hot water and soap might not really get the job done.

  14. Pizza can be a nutritious snack or meal, but the tasty food often comes laden with fat and calories.
    Net offers two important top toaster oven accessories which are
    the hanging toaster mound and the rotisserie kit. It is not like ordinary dough, it is not supposed to be soft as a baby’s bottom.

  15. Carpeting extractors might equally be applied that will take out slimy mold these types of when chemicals, cook skin oils, chewing your food gumline and so on

  17. Does your site have a contact page? I’m having a tough time locating it but, I’d like to send you an
    email. I’ve got some creative ideas for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it grow over time.

  18. Inside universities your common strategy for cleaning rugs and carpets utilizing an important container with awesome water and soap might not really function.

  20. Around academic institutions your standard solution for cleaning rugs and carpets using a fabulous container in awesome soap and water should possibly not operate.

  22. Hello! This is kind of off topic but I need some advice from
    an established blog. Is it very hard to set up your own blog?
    I’m not very techincal but I can figure things out pretty fast. I’m
    thinking about making my own but I’m not sure where to start. Do you have any points or suggestions? With thanks

  23. I feel that is among the such a lot vital info for me.
    And i’m happy reading your article. However want to commentary on few common things, The web site taste is ideal, the articles is truly nice : D. Excellent activity, cheers

  24. Body building needs the exact same.You need to integrate the appropriate tools to become profitable. Hypergh 14x UK The ideas you’ve read through listed here are facts you become successful in developing your regular workout.

  27. Hi there, simply turned into aware of your weblog through Google, and found that it is really informative. I’m going to be careful for brussels. I will be grateful if you continue this in future. Many people shall be benefited out of your writing. Cheers!

  29. Hello there, simply turned into aware of your blog thru Google, and found that it is really informative. I’m gonna watch out for brussels. I will appreciate if you continue this in future. A lot of other folks will be benefited from your writing. Cheers!

  31. I have been examinating out a few of your articles and it’s pretty good stuff. I will definitely bookmark your website.

  32. I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme? Exceptional work!

  35. Yоu сould dеfinitely see your ѕκills wіthin the worκ
    уou ωrite. The sectoг hoρes for even more pasѕionаtе writегs lіke you who aгe
    not afraiԁ tο say hοw they
    bеlieѵe. At all times fοllow your hеaгt.

    Heгe is my webpagе :: eladó lakások

  39. For mοѕt гecent infoгmation you hаve to viѕit ωеb
    anԁ on web I found thiѕ web ѕite aѕ а best
    ѕite fοr hοttest upԁatеs.

  40. I loved as much аs you’ll receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get bought an nervousness over that you wish be delivering the following. unwell unquestionably come more formerly again as exactly the same nearly very often inside case you shield this increase.

    Feel free to surf to my weblog My Vegas Business review

  41. I like the priceless info you supply with your articles.I’ll bookmark your web site and check out yet again listed here continually.I am pretty certainly I’ll realize a great deal of new things appropriate listed here! Beneficial luck to the upcoming!

  45. Remarkable things here. I am very glad to see your article. Thank you so much and I am having a look forward to contact you. Will you kindly drop me a e-mail?

  46. Consider running a сontest or offerіng
    a freе proԁuct tip sheet that accomрaniеs the
    interview. Perhaрs the beѕt thіng to
    do is keep an eye οn the promotional
    deаls and be reаdy to ρounce quicklу when a suіtable one cοmeѕ up.

    This not οnly allows listеners to listеn to thеir faνoritе MР3 files along ωіth theіr SIRIUS radio; іt аlso lets them digitally rеcord their favorite гaԁio songs foг inѕtant plаybаck.

  49. I just like the treasured details you produce inside of your posts.I’ll bookmark your blog and investigate all over again here usually.I am quite confident I will discover quite a lot of latest things suitable here! Good luck for the next!

  50. Okay, I’ll admit I’m a little biased since I
    am the author of this article. So if you plan one, here are few more birthday games ideas for different themes of birthday parties.
    A kids birthday party games party at the park
    can be a blast on a very low budget.

Comments are closed.