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”

  3. Woah! I’m really loving the template/theme of this website. It’s simple, yet effective. A lot of times it’s very difficult to get that “perfect balance” between usability and visual appeal. I must say you’ve done a great job with this. Additionally, the blog loads extremely quick for me on Internet explorer. Exceptional Blog!

  9. Excellent goods from you, man. I’ve understand your stuff previous to and you’re just extremely wonderful. 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 take care of to keep it wise. I can not wait to read far more from you. This is actually a tremendous web site.

  10. hey there and thank you for your info ¡V I¡¦ve definitely picked up anything new from right here. I did however expertise a few technical issues using this website, as I experienced to reload the web site a lot of times previous to I could get it to load correctly. I had been wondering if your hosting is OK? Not that I am complaining, but slow loading instances times will very frequently affect your placement in google and can damage your quality score if ads and marketing with Adwords. Anyway I¡¦m adding this RSS to my e-mail and could look out for much more of your respective interesting content. Ensure that you update this again soon..

  12. Nice post. I was checking continuously this blog and I’m impressed! Extremely useful info specially the last part 🙂 I care for such information a lot. I was seeking this particular information for a long time. Thank you and best of luck.

  13. I was examining some of your blog posts on this internet site and I believe this site is very informative ! Retain posting .

  14. I was examining some of your posts on this internet site and I think this website is really instructive! Keep posting .

  18. Simply want to say your article is as astounding. The clearness on your put up is just spectacular and i could assume you are an expert on this subject. Fine along with your permission allow me to seize your feed to keep up to date with approaching post. Thank you a million and please keep up the rewarding work.

  19. It’s really a nice and useful piece of information. I am happy that you just shared this useful information with us. Please keep us up to date like this. Thanks for sharing.

  21. Hi there! This blog post could not be written much better! Looking at this post reminds me of my previous roommate! He constantly kept talking about this. I’ll forward this post to him. Fairly certain he’s going to have a very good read. Many thanks for sharing!

  26. Keep up the fantastic piece of work, I read few posts on this web site and I think that your weblog is very interesting and has got bands of great information.

  28. I am really inspired along with your writing abilities as smartly as with the layout for your blog. Is that this a paid topic or did you modify it your self? Either way keep up the nice quality writing, it’s rare to see a great weblog like this one today..

  30. When I tend not to concur with you, I uncover your shipping of the view one that I’m able to actually locate pleasurable to hear to.
    My experiences with science and character have undoubtedly been distinctive.
    I definitely admire your considering.
    I must choose an opposing look at on “vulcanism”, nonetheless. Like a pretty untypical girl, I have been informed I am extremely logical and analytical. And, like the normal female, We have triggers that make me cry, but it could possibly cause you to giggle.
    I come across myself most pissed off and pushed into tears by people today behaving illogically. I eventually had a boyfriend take a look at me and say, “That’s just it. Men and women never make sense primarily. You need to prevent anticipating them to, and after that you may be a lot less disappointed significantly less typically.”
    Just thought you would possibly have a giggle from me.

  31. Good day I am so grateful I found your weblog, I really found you by error, while I was researching on Askjeeve for something else, Regardless I am here now and would just like to say thanks for a fantastic post and a all round entertaining blog (I also love the theme/design), I don’t have time to go through it all at the moment but I have saved it and also included your RSS feeds, so when I have time I will be back to read more, Please do keep up the excellent job.

  33. And that is the first rule of keywords – Relevance.

    You need to utilise every tool and potential outlet you possibly can.
    Make sure that your subscription box is very noticeable.

  34. Hey there, I think your site might be having browser compatibility issues. When I look at your blog in Ie, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, terrific blog!

  35. It is perfect time to make some plans for the future and it is 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 can write next articles referring to this article. I wish to read more things about it!

  36. I got this web page from my pal who informed me on the topic of this website and now this time I am browsing this website and reading very informative content at this time.

  38. With havin so much written content do you ever run into any issues of plagorism or copyright violation? My blog has a lot of unique content I’ve either authored myself or outsourced but it appears a lot of it is popping it up all over the internet without my permission. Do you know any ways to help prevent content from being ripped off? I’d really appreciate it.

  39. The new Zune browser is surprisingly good, but not as good as the iPod’s. It works well, but isn’t as fast as Safari, and has a clunkier interface. If you occasionally plan on using the web browser that’s not an issue, but if you’re planning to browse the web alot from your PMP then the iPod’s larger screen and better browser may be important.

  40. Hey There. I found your blog using msn. This is an extremely well written article. I’ll make sure to bookmark it and come back to read more of your useful information. Thanks for the post. I’ll certainly comeback.

  41. We are a gaggle of volunteers and starting a new scheme in our community. Your web site provided us with useful info to work on. You’ve performed a formidable job and our entire neighborhood will be grateful to you.

  47. Thank you for another magnificent article. Where else could anybody get that type of info in such an ideal way of writing? I have a presentation next week, and I’m on the look for such information.

  49. That is really interesting, You are an excessively skilled blogger. I’ve joined your feed and sit up for in quest of more of your fantastic post. Additionally, I have shared your web site in my social networks

Comments are closed.