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’ve been exploring for a little for any high quality articles or blog posts in this sort of area . Exploring in Yahoo I ultimately stumbled upon this web site. Reading this info So i’m happy to convey that I have a very good uncanny feeling I discovered exactly what I needed. I most unquestionably will make certain to do not fail to remember this site and provides it a look on a constant basis.

  3. Usually this collateral is commercial or residential real estate investments and loans can quickly become ruinous for anyone that is provided by private investors, through brokers. As a hard money lender. That mortgage broker to put up collateral, 99 of the economic kind.

  5. I love your blog.. very nice colors & theme. Did you design this website yourself
    or did you hire someone to do it for you?

    Plz respond as I’m looking to design my own blog and would like to find out where u got this from. kudos

  7. You already know thus significantly on the subject of this matter, produced me for my part imagine it from a lot of numerous angles. Its like men and women are not fascinated except it is one thing to do with Woman gaga! Your personal stuffs nice. All the time care for it up!

  9. I believe this is among the so much significant info for me. And i’m satisfied studying your article. But want to statement on few general issues, The website style is perfect, the articles is truly excellent : D. Just right process, cheers

  10. Hey there! I’m at work browsing your blog from my new iphone 4! Just wanted to say I love reading your blog and look forward to all your posts! Carry on the excellent work!

  12. Although the lender how you make a positive and personal impact on someones life. 5. What are the most legwork associated with secured loans because an unsecured loan for going on vacations raises many questions. But what if you only have to spend a lesser amount of money. Not only with their money on just your signature saying you’ll repay the bank the various terms and repayment schedules.

  13. Sharing some thing is better than keeping up-to our self, therefore the YouTube video that is posted at this time I am going to share by my relatives and mates.

  20. It’s remarkable designed for me to have a web site, which is valuable designed for my experience. thanks admin

  22. Hello there! Just discovered your site while i was browsing and i must say that i found it quite interesting! I hope you don’t mind if i return here from time to time and check your content…Meanwhile, if anyone is interesting in FREE FULL MOVIES, just follow this link: http://fastvisit.tk/FreeFullMovies It’a great resource, Enjoy it! Regards.

  24. naturally like your website but you need to take a look at the spelling on quite a few of your posts. Several of them are rife with spelling problems and I to find it very troublesome to tell the reality however I will surely come back again.

  25. My brother suggested I would possibly like this website. He used to be totally right. This publish truly made my day. You can not consider simply how much time I had spent for this information! Thank you!

  29. Have complete details on all of these types of service to the custodian company and your deals close smoothly. The more understanding an individual or a business idea that you want to do what they’re supposed to do a lot of equity remaining in it.2. Inspect the property will be.6. The proof of your LTV.

  31. I am a keen letter writer, and would like to know if there is anyway of creating a letter writing community by means of this. Creative letter writers writing to creative letter writers the earth over. I’ll reply to all those who wish to write and not just receive. 51 Lavender Sweep, London SW11 1DY. England

  32. Hello! I could have sworn I’ve been to this internet site before but after browsing via some of the post I realized it’s new to me. Nonetheless, I’m undoubtedly happy I found it and I’ll be book-marking and checking back regularly!

  38. We stumbled over here coming from a different website and thought I may as well check things out. I like what I see so now i am following you. Look forward to finding out about your web page yet again.

  39. I am extremely impressed with your writing skills as well as with the layout on your blog. Is this a paid theme or did you modify it yourself? Anyway keep up the excellent quality writing, it is rare to see a nice blog like this one today..

  41. I like the helpful info you provide in your articles. I will bookmark your weblog and check again here frequently. I am quite certain I¡¦ll learn a lot of new stuff right here! Best of luck for the next!

  42. I think this is among the most important information for me. And i am glad reading your article. But wanna remark on some general things, The website style is perfect, the articles is really excellent : D. Good job, cheers

  47. I was examining some of your articles on this internet site and I believe this site is very instructive! Retain putting up.

  48. Today, I went to the beach with my children. I found a
    sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She placed the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear.
    She never wants to go back! LoL I know this is totally
    off topic but I had to tell someone!

  49. yοu’re in point of fact a good webmaster. The website loading velocity is amazing. It seems that you are doing any distinctive trick. Moreover, The contents are masterpiece. you’ve
    ρeгfοrmed а magnifісеnt task
    іn thіs subjeсt!

  50. I think other website proprietors should take this internet site as an example , very clean and excellent user genial design .

Comments are closed.