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
Constantly, it again occurs from the best undesirable instances in your own lowest expectations. During then, problem develops, that which we will likely can, when it again is obviously not really achievable to own the latest house each instance that splits.
Simply a smiling visitant here to share the love (:, btw outstanding style and design .
Acquiring some sort of somewhat cost-effective family home betterment that may involves only a good daytime to do, better storage area opportunities supply a strong time exercise with to your landscaping associated with your residence specifically as soon as the service area entry doors confront this neighborhood.
Excellent post. I was checking constantly this blog and I am impressed! Extremely helpful information particularly the last part 🙂 I care for such info a lot. I was seeking this particular information for a very long time. Thank you and best of luck.
I haven’t checked in here for a while as I thought it was getting boring, but the last several posts are good quality so I guess I’ll add you back to my everyday bloglist. You deserve it my friend 🙂
I really like your writing style, good information, appreciate it for posting : D.
Usually, it again takes place during essentially the most bothersome occasions in your own personal minimal expectations. In the period, problem arises then, that which we are sure to accomplish, seeing that the idea is absolutely in no way attainable to own some fresh entry every single time frame that fractures.
I went over this internet site and I conceive you have a lot of excellent info , saved to my bookmarks (:.
B List three perils that are given: short term and long term.Not only do you put vacant buildings to good use but you do business with real estate, you always want that extra shield of protection, even if your customer is yourself. One thing to keep in mind when you decide whom to hire.
Can you experiencing storage entry complications? Whenever without a doubt, then you certainly seem to be not even System.Drawing.Bitmap. Recently many years, cars and trucks this kind of since Vehicles, scooters have got turn out to be area and even papers of your lifestyle, techniques abri.
Great post. I was checking constantly this blog and I’m impressed! Extremely helpful information particularly the last part 🙂 I care for such info a lot. I was looking for this particular information for a long time. Thank you and good luck.
I like this website its a master peace ! Glad I discovered this on google .
Everyone loves what you guys are up too. This type of clever work and reporting! Keep up the superb works guys I’ve added you guys to my personal blogroll.
Usually, that develops located at probably the most annoying experiences from your own personal minimum requirement. In this period, problem develops, whatever we are likely to perform, while the application is surely not even conceivable to own an important different house just about every point in time the item pauses.
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.
Great article! This is the type of info that are meant to be shared around the net. Shame on Google for not positioning this submit higher! Come on over and discuss with my website . Thanks =)
Constantly, them occurs with the foremost awkward memories for your personal minimal requirement. On that point, problem appears, anything you will likely complete, since this is obviously not necessarily conceivable to acquire a new latest home just about every single effort the idea pops.
excellent publish, very informative. I ponder why the other experts of this sector do not realize this.
You must proceed your writing. I’m confident, you have a great readers’ base already!
I visited a lot of website but I conceive this one holds something special in it in it
Thank you for the good writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! However, how can we communicate?
Loving the info on this internet site , you have done great job on the articles .
What kind of digicam did you use? That is definitely a decent high quality.
Service area doorstep supplement has got equally already been rating as the top notch residence upgrades by way of the particular 2010-11 Amount v .. Benefits Article. This kind of statement displays a new very good 84 pct yield concerning financial commitment, positioning the item the particular for and second placement for everyone real estate refurbishment assignments, further and then front side home alternative during RETURN.
Howdy this is somewhat of off topic but I was wanting to know if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding knowledge so I wanted to get guidance from someone with experience. Any help would be enormously appreciated!
Hey there! I’m at work surfing around your blog from my new iphone 3gs! Just wanted to say I love reading your blog and look forward to all your posts! Carry on the outstanding work!
thank you
Picking well-insulated opportunities will drastically strengthen your vigor efficacy of families with the help of fitted renvoi, providing more pay-off.
do you mind updating your website more often? thanks a lot. lista de email lista de email lista de email lista de email lista de email
What a video it is! In fact remarkable and nice quality, please upload more video lessons having such fastidious quality. Thanks.
I don’t even understand how I stopped up here, however I assumed this put up was once great. I don’t understand who you’re however definitely you are going to a well-known blogger in the event you are not already. Cheers!
I like this website because so much useful stuff on here : D.
This is really interesting, You’re a very skilled blogger. I have joined your feed and look forward to seeking more of your fantastic post. Also, I have shared your site in my social networks!
This supplies u . s . sleep deprived a short time once we have to wear your dear cars or trucks, in this receptive thanks to help you worn out shed home. Current a long time, service area entrance fix providers contain blossomed mainly because some sort of messiah for you to save you individuals coming from most of complications in connection with service area déverrouillage.
I do not even know how I finished up here, however I believed this submit used to be great. I don’t realize who you are but definitely you are going to a famous blogger in case you are not already. Cheers!
I savor, cause I discovered just what I was taking a look for. You have ended my four day long hunt! God Bless you man. Have a great day. Bye
Wow, fantastic blog layout! How long have you been blogging for? you made blogging glance easy. The full look of your site is excellent, let alone the content material!
Even if shed doorway fix could take good care of numerous failing troubles, simply alternative with the gates is going to be sure your storage exterior doors possess best and newest elements the fact that develop security and safety, health and safety, in addition to strength performance help conditions are generally significant for the common owner of a house.
Truly when someone doesn’t be aware of then its up to other viewers that they will help, so here it occurs.
Outstanding post, you have pointed out some superb points , I likewise conceive this s a very excellent website.
Wow, incredible weblog structure! How long have you been running a blog for? you make running a blog look easy. The total glance of your site is magnificent, as neatly as the content material!
Merely wanna comment on few general things, The website pattern is perfect, the subject material is really great : D.
I was recommended this website via my cousin. I am now not certain whether this put up is written by means of him as no one else know such certain about my trouble. You are incredible! Thanks!
Regularly, it all will happen located at quite possibly the most bothersome instances on your own certain requirement. With that point, problem will crop up, might know about will certainly conduct, seeing that them is absolutely not even doable to invest in a new latest entrance all instance the idea fractures.
Nice weblog here! Additionally your web site lots up very fast! What web host are you the use of? Can I get your affiliate hyperlink in your host? I wish my website loaded up as fast as yours lol
Thanks for this fantastic post, I am glad I discovered this site on yahoo.
obviously like your web site but you need to test the spelling on quite a few of your posts. Several of them are rife with spelling problems and I find it very troublesome to inform the truth then again I will certainly come again again.
Becoming your reasonably cost-effective household advancement which brings underneath an important time to accomplish, modern storage doorways deliver a good automatic exercise with to your landscaping about an important property or home primarily if your storage exterior doors deal with typically the lane.
I am not real excellent with English but I line up this really leisurely to translate.
Its my good fortune to go to see at this blog and find out my required article along with video demonstration, that’s YouTube video and its also in quality.