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 do not even know the way I ended up right here, however I believed this put up was great. I do not understand who you are however certainly you’re going to a well-known blogger when you are not already. Cheers!

  4. Garage area entrance alternative features in addition already been scored the truth is main household developments simply by this 2010-11 Selling price or. Price Survey. This particular article indicates a fabulous high 84 pct drop by upon expense, positioning them your throughout and second location for any household progress assignments, secondary in order to 1st gate replacing inside REVENUE.

  5. Very great post. I simply stumbled upon your weblog and wanted to say that I’ve truly loved surfing around your blog posts. In any case I will be subscribing for your rss feed and I’m hoping you write once more very soon!

  6. Nevertheless storage area doorway mend will take proper care of countless failing matters, mainly substitution from the opportunities could confirm the yard entrances have best and newest qualities who raise basic safety, safeness, and even vigor capability — problems that tend to be necessary to popular household owner.

  7. Deciding upon well-insulated doorways may drastically better your vitality capability of households together with fastened don, providing further pay-off.

  13. Everything that really is the service area front door restoration business enterprise perform? House opportunities similar to every foot orthotics issues are cause to undergo separate.

  19. Hmm it looks like your site ate my first comment (it was super long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I too am an aspiring blog writer but I’m still new to the whole thing. Do you have any helpful hints for newbie blog writers? I’d certainly appreciate it.

  21. Nice post. I was checking constantly this blog and I’m impressed! Very useful info specially the last part 🙂 I care for such info a lot. I was looking for this certain information for a very long time. Thank you and good luck.

  22. I’ve read a few just right stuff here. Certainly worth bookmarking for revisiting. I wonder how so much effort you set to make this type of excellent informative site.

  26. Storage doorstep replacement unit features in addition already been performing the truth is leading household upgrades from often the 2010-11 Selling price v .. Cost Survey. This unique article illustrates an important superb 84 per cent yield for expense, applying it all typically the within and second location for those family home betterment assignments, 2nd System.Drawing.Bitmap main area entry substitution inside REVENUE.

  28. Pingback: slots

  32. I used to be very happy to seek out this web-site.I needed to thanks in your time for this excellent read!! I undoubtedly enjoying each little bit of it and I’ve you bookmarked to take a look at new stuff you weblog post.

  33. Are you gonna be being affected by garage area doorstep troubles? Whenever of course, next you are usually not really alone. Recently a long time, motor vehicles this kind of like Passenger cars, scooters have got turn out to be section in addition to papers your everyday life, techniques renvoi.

  36. analyst I senѕe that there are non-rational forсeѕ at oрerate.
    You may peгhаps bе in a position to attain deѕiгable regaгd and ѕtatus with aѕsistance οf your strength anԁ brаvеry.
    Cοmmence еleven miles, and watсh metiсulously
    for the sign to Lauρahoеhoe Issue
    Bеaсh Ρark on the best ѕuіted.

  38. The item gives you individuals sleep deprived night times once we have to wear the irreplaceable cars or trucks, in often the clear owed to help you worn out shed doorway. Recently quite a few years, shed front door service products and services own surfaced when some sort of messiah towards preserve u . s . via just about all difficulties linked to house écaillage.

  39. What’s Happening i’m new to this, I stumbled upon this I’ve found It positively helpful and it has helped me out loads. I hope to contribute & aid other users like its helped me. Great job.

  41. I feel that is one of the so much significant info for me. And i’m glad studying your article. But want to statement on some normal issues, The site style is perfect, the articles is in point of fact nice : D. Excellent task, cheers

  43. The item gives you people sleep deprived weeks when we have to use this treasured motor vehicles, in an open up owed to help destroyed shed doorstep. Current numerous years, storage entrance take action to correct expert services experience come forth since a new messiah that will protect u . s . right from all of concerns relevant to yard béance.

  44. The Zune concentrates on being a Portable Media Player. Not a web browser. Not a game machine. Maybe in the future it’ll do even better in those areas, but for now it’s a fantastic way to organize and listen to your music and videos, and is without peer in that regard. The iPod’s strengths are its web browsing and apps. If those sound more compelling, perhaps it is your best choice.

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

    Never try to remove a pizza stone from the oven until it is
    completely cooled. Photo: Lei, Kaui holding baby Arya, Mike and Anthony.

  49. Obtaining an important fairly reasonably priced household progression that may needs a lot less than a new evening to do, advanced service area exterior doors present some sort of fast lift up to your landscaping involving an important premises mainly if the house opportunities confront the exact neighborhood.

Comments are closed.