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
I much like the worthwhile data you provide on your posts.I’ll bookmark your weblog and take a look at once more right here repeatedly.I am particularly absolutely sure I will know a lot of new stuff right below! Beneficial luck for your following!
Wow, this paragraph is fastidious, my sister is analyzing these kinds of things, therefore I am going to convey her.
{Specific doctors build a complex therapy that includes each patient’s individual needs and difficulties, to be able to be successful the reintegration within the healthy and normal life from the modern society. The issue for the addict is the fact society has attached a stigma to the condition.|This pattern has actually taken over their own life – they spend alot of their time thinking about obtaining the next dosage so they avoid go through drawback. Currently methadone is really a big factor in prescription medication addiction epidemic, and also the overdose data are escalating.|Medical related drug detox performed the right way is not really “one dimension suits all”. There is an increased risk of dependancy and abuse while administering oxycontin. Interestingly enough, the state of Az has among the most severe drug problems in the usa.|The most typical you are related to the individual’s mindset regarding the suboxone therapy itself. But digging a little deeper in to the statistics, we start to observe how polydrug hazards eclipse individual deaths. A lot of in society assert that the addict is entirely accountable.|(Unfortunately, their are lots of typical buprenorphine suboxone mistakes made by individuals and treating medical professionals. Clinical drug detox done the correct way is not “one dimension matches all”. There are a lot connected with options other than doctor prescribed painkillers to deal with moderate pain.|But too many people, whether making use of oxycontin legally or perhaps abusing it recreationally, are finding out just how rapidly usually it takes more than their lifestyles. | This is actually the problem I see with many individuals who simplistically check out being about the medication as the sole way to pass though opiate habit.|However 10 times as numerous deaths through prescription drugs in combination with other drugs shows that polydrug mistreatment is a brand-new and very serious problem.
Spot on with this write-up, I honestly believe this website needs a lot
more attention. I’ll probably be returning to read through more, thanks for the information!
Wow, incredible weblog format! How lengthy have you been blogging for? you make running a blog glance easy. The whole glance of your site is great, let alone the content!
Good website! I really love how it is easy on my eyes and the data are well written. I’m wondering how I could be notified whenever a new post has been made. I have subscribed to your RSS which must do the trick! Have a nice day!
Following, good pictures are the must. This particular isnt saying we must come to be equipped to see a new pimple previously mentioned the most fresh characters remaining eyebrow, although that we needs to be equipped to see a good few details which can be pertinent to the video game.
The FHA streamline personal loanss. These unpaid amounts are considered company assets as it is difficult to pay the whole sum back at one time.
I think other web-site proprietors should take this site as an model, very clean and excellent user genial style and design, as well as the content. You are an expert in this topic!
I’m thrilled with getting a brand new mandibular advancement splint, it’s great for reducing my noisy snoring so now my husband can rest. It’s great for anyone who suffers from obstructive sleep apnoea.
Jones said he attempted to mediate a dispute between Francis
and Wynn’s casino over $2 million in gambling losses. http://www.fabnewonlinecasino.co.uk/ Mr McCann said his company had been working on the funding for some time with partners Canada Pension Plan, Australian Prime Property Commercial Fund, Telstra Super and First State Super.
I such as the worthwhile details you supply into your reports.I will bookmark your web log and check once again the following recurrently.I’m relatively sure I will understand a great deal of new things proper right here! Really good luck to the next!
This is a great tip especially to those new to the blogosphere. Simple but very precise information… Thanks for sharing this one. A must read post!
due to the fact I’m this kind of some sort of fan of words and phrases, I actually love the match Taboo. It can your totally addictive game for some participants where you have got to for you to have your companion to say the several word not having uttering that concept – or maybe almost any form of this – on your own. It’s a good enormous task, nevertheless one that’s a great complete hoot!
Therefore , the lot of some income is spent with games every 12 months. More more than, presently there are free variants or even free games readily available for kids whom cannot pay together with after they expand up and they are in a position to play these upgrade to paid out types of the activities. This will make this seem like now there will be many years of so used gamers when the trend carries on.
Subsequent, good layouts are a good must. That isnt saying we have to come to be in a position to see a good pimple previously the most more youthful characters quit eyebrow, yet that we needs to be ready to see some sort of few details which have been appropriate to the video game.
Excellent publish, thank you so much for sharing. Do you have an RSS feed I can subscribe to?
You need to take part in a contest for one of the best sites on the web.
I’m going to recommend this website!
hyperhidrosis leads to excessive sweating is sage. More and more sweating.
The surgeon then inserts a fiber-optic camera and clip
the nerve pain that accompanies it.
You have made some good points there. I looked on the web for more information about the issue and found most individuals will go along with your views on this website.
From the perspective of the revolution to transform fossil fuel consumption into a greener energy by purchasing an electric bicycle to save money on gas? You can pass by cars that are held up by a traffic light or a slight accident.
Do you mind if I quote a few of your articles as long
as I provide credit and sources back to your weblog? My website
is in the exact same area of interest as yours and my visitors would certainly
benefit from some of the information you present here.
Please let me know if this alright with you.
Regards!
You’re truly a excellent webmaster. The site loading pace is amazing. It kind of feels that you’re doing any unique trick. In addition, The contents are masterwork. you’ve done a great activity in this topic!
Hi, i think that i saw you visited my site thus i came to “return the favor”.I am trying to find things to enhance my site!I suppose its ok to use a few of your ideas!!
this might be gonna get hairy!
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.
Follow this simple directions and you will be amazed how good this pizza recipe really is.
Sprinkle the top with Italian seasonings for garnish and additional
flavor. Add the water a teaspoon at a time so it’s not over done.
Oh my goodness! Incredible article dude! Thank you so much,
However I am encountering issues with your RSS. I don’t understand why I am unable to subscribe to it. Is there anyone else getting the same RSS problems? Anyone that knows the answer can you kindly respond? Thanks!!
It¡¦s in point of fact a great and helpful piece of info. I am satisfied that you just shared this useful info with us. Please keep us informed like this. Thanks for sharing.
magnificent submit, very informative. I ponder why the other experts of this sector do not notice this. You must proceed your writing. I am sure, you have a huge readers’ base already!
I am curious to find out what blog platform you happen to be using? I’m having some minor security issues with my latest site and I would like to find something more risk-free. Do you have any suggestions?
Hi there! This post could not be written any better! Reading through this post reminds me of my good old room mate! He always kept chatting about this. I will forward this write-up to him. Pretty sure he will have a good read. Thanks for sharing!
I am thrilled with getting a brand new set of ear plugs, I use them for helping cut out my wife’s horrendous snoring so now I can get some rest. They’d be great for anybody who’s sleeping partner snores.
you’re really a good webmaster. The site loading speed is amazing. It seems that you’re doing any unique trick. Also, The contents are masterpiece. you have done a excellent job on this topic!
Hola! I’ve been reading your blog for a while now and finally got the courage to go ahead and give you a shout out from Atascocita Texas! Just wanted to say keep up the great work!
Hmm it looks 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 the whole thing. Do you have any tips and hints for beginner blog writers? I’d genuinely appreciate it.
Therefore, you will contribute to your imperative need to give the lending company should a co-signer in the first cent. For example, if you fail to pay first. There are exceptions to these rules, but you should watch out for.
It’s my belief that mesothelioma is actually the most lethal cancer. It contains unusual features. The more I actually look at it a lot more I am confident it does not respond like a true solid cells cancer. When mesothelioma is really a rogue virus-like infection, in that case there is the probability of developing a vaccine plus offering vaccination for asbestos open people who are really at high risk connected with developing upcoming asbestos connected malignancies. Thanks for giving your ideas for this important ailment.
Woah this weblog is wonderful i love reading your articles. Keep up the great work! You recognize, many individuals are hunting round for this information, you could aid them greatly.
I’m curious to find out what blog platform you have been using? I’m having some minor security problems with
my latest site and I’d like to find something more safe. Do you have any recommendations?
It’s really a great and useful piece of info. I am happy that you simply shared this useful info with us. Please stay us informed like this. Thanks for sharing.
In favor of my reading purposes, I every time used to download the video lectures from YouTube, for the reason that it is straightforward to fan-out from there.
An impressive share! I’ve just forwarded this onto a colleague who has been conducting a little homework on this. And he in fact bought me breakfast simply because I discovered it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanx for spending the time to talk about this topic here on your web site.
Strеtch to Gгow Taller 4 Idiots both naturally and sаfely.
WOW just what I was looking for. Came here by searching for %keyword%
Hey, you used to write excellent, but the last few posts have been kinda boring… I miss your super writings. Past several posts are just a little bit out of track! come on!
You made some really good points there. I looked on the web for additional information about the issue and found most people will go along with your views on this site.
obviously like your web-site however you have to check the spelling on quite a few of your posts. Several of them are rife with spelling problems and I find it very troublesome to tell the truth nevertheless I¡¦ll certainly come again again.
First off I want to say superb blog! I had a quick question in which I’d like to ask if you do not mind. I was interested to find out how you center yourself and clear your mind prior to writing. I’ve had trouble clearing my mind in getting my thoughts out. I do take pleasure in writing however it just seems like the first 10 to 15 minutes are usually lost simply just trying to figure out how to begin. Any ideas or hints? Kudos!