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”

  4. Hi theгe! ӏ know this is kinda off toρic but Ӏ was wonԁering ωhich blοg ρlatform are you
    using for thiѕ website? I’m getting sick and tired of WordPress because I’νe hаԁ іsѕuеѕ with hacκeгѕ аnd I’m looking at alternatives for another platform. I would be awesome if you could point me in the direction of a good platform.

  5. Apple now has Rhapsody as an app, which is a great start, but it is currently hampered by the inability to store locally on your iPod, and has a dismal 64kbps bit rate. If this changes, then it will somewhat negate this advantage for the Zune, but the 10 songs per month will still be a big plus in Zune Pass’ favor.

  7. Nevertheless garage area entry remedy could take good care of lots of breaking down troubles, basically substitution with the entrances might make sure that a house entry doors possess best and newest benefits of which boost safety, safeness, as well as power productivity tutorial problems that tend to be significant on the standard prroperty owner.

  13. Helpful information. Lucky me I found your website by accident, and I am surprised why this twist of fate didn’t happened earlier! I bookmarked it.

  14. I love reading and I think this website got some really useful stuff on it! .

  15. Pretty nice post. I just stumbled upon your blog and wanted to say that I have truly enjoyed surfing around your blog posts. In any case I will be subscribing to your feed and I hope you write again very soon!

  16. Car port doorstep renewal includes moreover become ranked among the leading place developments by means of your 2010-11 Selling price compared to. Benefit Survey. That record presents a new quite high 84 pct go back upon financial commitment, positioning this often the within and second status for anyone place progress undertakings, 2nd merely to top entrance renewal for REVENUE.

  17. Hеllo, its fastidious piecе of wrіting about mediа prіnt, ωe
    all understand media is a wonderful sourсе of facts.

    Here is my wеb blοg; laptop

  19. 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.

  22. The things accurately is an important car port doorstep take action to correct provider carry out? Garage area gates prefer most of physical details are be subject to splitup.

  24. Staying a fabulous moderately low-priced family home advancement that will involves not as much as some time to do, enhanced car port exterior doors produce a particular swift boost to landscaping regarding a good asset mainly if the car port opportunities have to deal with the exact avenue.

  25. It’s perfect time to make some plans for the long run and it is time to be happy. I have read this submit and if I may just I wish to suggest you few fascinating things or tips. Perhaps you could write next articles relating to this article. I desire to read more things about it!

  26. Hmm it appears like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I wrote and say, I’m thoroughly enjoying your blog. I too am an aspiring blog blogger but I’m still new to everything. Do you have any suggestions for novice blog writers? I’d really appreciate it.

  27. Most often, it again comes about on just about the most undesirable occasions by the minimal expectations. From time, problem appears, everything we will likely conduct, since it again is undoubtedly definitely not probable to purchase some sort of different doorway each individual time period the idea pops.

  28. We absοlutеly love yοur blоg аnԁ find
    most οf уour post’s to be precisely what I’m
    loοkіng for. Does one οffer gueѕt writeгѕ to write сontent for yοu?

    I wouldn’t mind producing a post or elaborating on most of the subjects you write with regards to here. Again, awesome web site!

  29. When you’re learning chords, you learn and study them for only One Key at a time. — Notes: Bb D F. Pretend that we are palming an egg and keep your arms relaxed and fingers curled.

  31. ”’. Then you can quickly and easily figure out how to play any chord in any key on the spot, whether you’ve memorized that specific chord or not.
    Best Regards.

  32. Thanks for some other wonderful post. The place else could anyone get that kind of info in such a perfect way of writing? I’ve a presentation subsequent week, and I’m on the search for such info.

  38. Pretty great post. I simply stumbled upon your weblog and wanted to mention that I’ve truly loved browsing your weblog posts. After all I’ll be subscribing in your feed and I’m hoping you write once more soon!

  39. I got so bored at the moment afternoon, but as soon as I watched this YouTube comic clip at this web site I become fresh and happy too.

  40. hey there and thank you for your information – I have definitely picked up anything new from
    right here. I did however expertise several technical issues using this website, as I experienced to reload the site many times previous to
    I could get it to load properly. I had been wondering if your web hosting is OK?
    Not that I’m complaining, but slow loading instances times will sometimes affect your placement in google and could damage your quality score if ads and marketing with Adwords. Anyway I’m adding
    this RSS to my email and could look out for a lot more of your
    respective intriguing content. Ensure that you update this again very soon.

  41. naturally like your web site but you need to test the spelling on several of your posts. Many of them are rife with spelling problems and I find it very bothersome to tell the truth nevertheless I will certainly come back again.

  42. Aw, this was an extremely good post. Spending some time and actual effort to make a top notch article… but what can I say… I hesitate a whole lot and don’t seem to get anything done.

  43. hey there and thank you for your info ¡V I have definitely picked up something new from right here. I did however expertise several technical issues using this site, as I experienced to reload the website lots of times previous to I could get it to load properly. I had been wondering if your hosting is OK? Not that I am complaining, but sluggish loading instances times will often affect your placement in google and can damage your quality score if ads and marketing with Adwords. Anyway I am adding this RSS to my e-mail and could look out for much more of your respective interesting content. Make sure you update this again very soon..

  46. A person essentially assist to make seriously articles I’d state. This is the first time I frequented your web page and thus far? I amazed with the research you made to create this actual publish extraordinary. Great activity!

  47. We’re a group of volunteers and opening a new scheme in our community. Your web site provided us with valuable information to work on. You have done a formidable job and our entire community will be thankful to you.

  48. We are a group of volunteers and opening a new scheme in our community. Your website offered us with valuable info to work on. You have done a formidable job and our entire community will be grateful to you.

  49. One more thing that I would like to share at this place is that, whatsoever you are using free blogging service but if you don’t update your blog on daily basis then it’s no more attraction.

Comments are closed.