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. You already know therefore considerably when it comes to this subject, made me personally imagine it from numerous various angles. Its like men and women aren’t fascinated until it’s something to accomplish with Woman gaga! Your personal stuffs excellent. All the time maintain it up!

  2. Definitely believe that which you said. Your favorite reason appeared to be on the web the simplest thing to be aware of. I say to you, I certainly get irked while people consider worries that they plainly don’t know about. You managed to hit the nail upon the top as well as defined out the whole thing without having side-effects , people can take a signal. Will likely be back to get more. Thanks

  3. We’re a group of volunteers and starting a new scheme in our community. Your site offered us with valuable info to work on. You’ve done an impressive job and our whole community will be grateful to you.

  7. I love foregathering utile information , this post has got me even more info! .

  9. ” Pizza was called (and is still called) tomato pie and pizza pie in certain parts of the States. 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. Lay the dough on the tray or baking sheet, then dimple the dough until it’s a thick, flattened circle.

  10. I am no longer certain the place you are getting your information, but good topic. I needs to spend a while finding out more or working out more. Thanks for fantastic info I was looking for this information for my mission.

  14. Throughout school choices the very customary process for cleaning rugs and carpets through an important pail with incredibly hot water and soap may definitely not get the job done.

  15. Definitely believe that which you said. Your favorite reason appeared to be on the internet the easiest thing to be aware of. I say to you, I certainly get irked while people think about worries that they plainly do not know about. You managed to hit the nail upon the top and defined out the whole thing without having side-effects , people could take a signal. Will likely be back to get more. Thanks

  16. Your style is really unique compared to other folks I have read stuff from.
    Many thanks for posting when you’ve got the opportunity, Guess I’ll just bookmark this web site.

  18. Hello to every single one, it’s really a fastidious for me to go to see this web site, it consists of priceless Information.

  20. I am lucky that I discovered this site , just the right information that I was searching for! .

  23. In οtheг wοгԁs, they
    gο against the gгain of the careers thеіr parents had.
    Brazenhead is a great place to go ωith your family or for a businеsѕ
    lunсh or dinner, but іf yοu are looking for
    a рaгtу аtmosphеre, this iѕn’t it. The decline of the East side blues scene was disheartening, but, it also gave rise to the need for a fresh start, which came in the form of the next blues-only venue, Antone’s,
    founԁed bу the latе Ϲlifford Antоnе, during the
    summег of 1975.

  24. In relation to the last paragraph, the question had arisen by the people
    that how the diet can be eaten by us in the same form in which it was utilized by our ancestors.
    Let me tell you a quick story about the beauty of filling up on a paleo diet breakfast.
    Grains, salt, processed oils, legumes, and any other food that is
    not available yet during the said era are not including in this special type of diet.

  25. Encapsulation flooring chemicals are in addition very well-liked on classes because they are considerably more productive, will need not as jerking some are usually efficient as they simply throw away even less normal water since opposed to be able to additional solutions.

  27. Additionally, Im attractive certain that if I had been in her position I wouldn’t be stupid enough to flash some guy Ive never came across before in my being on a sex cam

  28. New carpet domestic cleaning is usually some hard undertaking most definitely inside locations certainly, there is higher targeted visitors similar to classes, places of work and so on

  29. Wow! This could be one particular of the most helpful blogs We have ever arrive across on this subject. Actually Wonderful. I am also an expert in this topic so I can understand your effort.

  32. It is the best time to make some plans for the future and it’s time to be happy. I have read this post and if I could I desire to suggest you few interesting things or tips. Maybe you could write next articles referring to this article. I desire to read even more things about it!

  33. Wow, wonderful blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is excellent, as well as the content!

  36. Most often, it again takes place located at one of the most ill-timed memories for your current lowest requirement. Located at the period, problem occurs, what we should will likely accomplish, since the idea is surely not really likely to own some different gate every single time frame that pauses.

  37. There are features that allow you to keep track of vendors that you like and keep notes so
    you know why they are listed. (Here’s the key difference. This site helps you find vendors within your local area.

  38. Acquiring some pretty affordable residence advancement that may needs a lot less than a good evening in order to complete, improvedadvanced car port panels provide you with a good fast exercise into the landscaping in some sort of residence most definitely if your storage area entrance doors skin the very lane.

  40. Despite the fact storage doorway take action to correct takes proper care of several breaking down troubles, simply substitution of this opportunities may be sure that storage exterior doors include the recent attributes of which raise protection, essential safety, not to mention electric power capability help issues which can be crucial to old classic household owner.

  41. Regularly, the item will happen during one of the most bothersome memories located at your company lowest span. In the period, problem will crop up, that which you will accomplish, like the application is unquestionably not even feasible to own some sort of unique doorway just about every point in time this breaks or cracks.

  43. Service area house replacing provides as well ended up positioned as the first rate house innovations as a result of your 2010-11 Expense as opposed to. Price File. That document demonstrates the superb 84 per-cent come home upon financial commitment, applying the item the exact inside and second spot for those family home refurbishment undertakings, secondary to top doorstep new on MOTOROLA ROI.

  46. This is getting a bit more subjective, but I much prefer the Zune Marketplace. The interface is colorful, has more flair, and some cool features like ‘Mixview’ that let you quickly see related albums, songs, or other users related to what you’re listening to. Clicking on one of those will center on that item, and another set of “neighbors” will come into view, allowing you to navigate around exploring by similar artists, songs, or users. Speaking of users, the Zune “Social” is also great fun, letting you find others with shared tastes and becoming friends with them. You then can listen to a playlist created based on an amalgamation of what all your friends are listening to, which is also enjoyable. Those concerned with privacy will be relieved to know you can prevent the public from seeing your personal listening habits if you so choose.

  48. I think this is among the most significant info for me. And i’m glad reading your article. But want to remark on few general things, The site style is wonderful, the articles is really great : D. Good job, cheers

  49. Usually, it all arises located at the best bothersome occasions in your own lowest expectations. On the period, problem develops, whatever we are inclined to perform, mainly because them is definitely not necessarily likely to invest in an important different doorstep each period the application concessions.

  50. Fantastic goods from you, man. I’ve understand your stuff previous to and you’re just extremely great.
    I actually like what you have acquired here, certainly like what you’re saying and the way in which you say it. You make it entertaining and you still care for to keep it smart. I can’t wait
    to read far more from you. This is really a tremendous site.

Comments are closed.