Creating Drop Down Lists In A Form

the next thing that we want to do with our database is create a form to enter in the orders so I'm going to again go ahead and go to the create menu right up here at the top and for this I'm going to go ahead and just use an auto form really quickly we're going to customize this a little bit this will give us a good starting point now you're going to see when I did that I actually created the an auto form for the customers table because I forgot to click on orders before I did so this is ok I can just simply right click here and close this and say Save Changes no and that form goes away so again I'm going to select the orders table right here then come to create and I'm going to go ahead and select form and that will create me a new blank form for the orders and I'm going to go ahead and save this as order entry form always remember to save an object after you create it so you can see here I can go from field to field entering in the information for in order now there's a problem here the problem is that when I want to enter in the customer ID or the product ID I need to know the correct number to put in its place so I'm going to have to be constantly referring back to the customers table or the products table to find the correct ID so that really is going to be a difficulty for this so we're going to do is we're going to go ahead and customize this form to make dropdowns for each one of these fields first we begin by making sure that we're in design view and not form view I do that by clicking the View button right here and clicking on design view now what we're going to go ahead and do is we're going to go ahead and replace this field with a drop-down I'm going to go ahead and delete this field right now you'll see it goes away and then what I'm going to go ahead and do is come up here to my form design tools area now this only appears when you're editing a form you'll see the section up here for Form design tools gives you a variety of different things that you can do with this form but what we need to do is we need to create a drop-down for our customer ID II and again all the controls are right here I'm going to click this bottom drop down here and you can see all the tools at once and this is the tool for a combo box or for a drop-down list and that's exactly what I want I'm going to go ahead and click that now I'm going to come down here into my form and I'm just going to go ahead and drag out a box and it's going to bring me into this combo box wizard and it's going to ask me what I exactly want to do in this case the default I want this combo box to get information or to get values from another table is correct I want to pull values out of the customers table for use in this order entry form so I'll go ahead and leave that selected and then click Next and now I've got to choose which table I want to pull the information out of in this case I'm pulling the information out of the customers table you could also pull information out of queries if you wanted to by selecting this option here and we'll do that in a little bit now I'm going to go ahead and click Next and it brings me to this dialog box and it's going to ask me which fields in the customers table do I want to include in this drop-down list well in this case I want to I need the customer ID so I'm going to add that but I also want the company name so that I have some way of identifying who that is so I'll go ahead and double click on company name you could also select any other one of these you wanted to for instance I can put state in here so I can see what state that company is located in so I'm going to have three values in my drop-down list now go ahead and click Next and it's going to ask me how do I want to sort the items and you can see here I can do a multi-level sort by anyway error by any one of four fields well in this case first thing I want to do is I want to group everybody in the same state together so I'm going to click this drop down here I'm going to say the first sort order is state the second sort order should be company name and again this is in ascending order I'll go ahead and click Next and it's going to show me how this drop down is going to appear and you can see our Arizona companies are at the top followed by the California companies you're also going to notice that I can actually see the customer ID number here and that's because I'm hiding this because it's a key column it's not actually useful information in the drop-down list well we are going to use that key column to populate the field inside of the the orders table so I'll go ahead and I'm going to make this a little bit smaller so we don't use quite so much space actually I make this a little larger and this a little bit smaller and then I'll go ahead and click Next and what do we want to do with that value I want to store the value from the key column from customer ID from the customers table in the customer ID field in the orders table so I'm pulling it out of customer ID out of the customers table and I'm placing it in the customer ID in the orders table then I'll click Next and we have to give this a name and I'm going to go ahead and just call this customer ID and I'll click finish and we now have a drop down list and I'm going to go ahead and actually I'm not going to go ahead and move that around anywhere you can see just the way this sort of looks you could position these make them look better but I'm going to go ahead and go to form view here and you're going to see I now have a drop-down list here so I can click this drop-down and select fun and stuff in Arizona and that's what we stored in the customer ID field so let's go ahead and product I'm just going to go ahead and type in 3 and whoops get that fun and stuff and quantity will be two shipping method will be let's say a 1 this is a rush order no comments and I'll create another record and then let's go ahead and see how that got entered in I'm going to go ahead and save that and open up the orders table and you can see order number one here I was able to select the correct customer ID from the list and it populated that with that number so that's it's a really quick way of adding a drop-down to give you more information about a particular field that you need to make a correct selection and really cut down on the errors in data entry now we can go ahead and do the exact same thing let's go back to order entry we can do the exact same thing for Product ID and I'm actually going to switch into view here and I'm just like layout view and what that's going to allow me to do is it's going to sort of control the way I place things in you'll remember I had to drag a box out here and you'll see they're not quite as long as the rest of them and it's a little bit off on the spacing here so in layout view access helps you with that so I'm going to go ahead and click on that again and press delete so it goes away and then I'm going to come up here and select my combo box tool and I'm going to come down here and can you see how access is highlighting the space that the former field occupied so now all I have to do is click and I'll create the combo box in that exact same spot combo box drop down list same thing it's a synonym for each other I want to pull the values out of a table the table I want to use is the products table I want to use the product ID because that's the value I need along with the product name and then I'm going to go ahead and click Next I'm going to sort these by the product name and then next you'll see the list of products here again we're hiding our key column and then next do we want to remember the value for later no we want to store the value we select from the products table in the drop down list in the product ID in the orders table so again we're pulling a value out of the products table to include in the orders table but in the Product ID column due next and I'm going to go ahead and call this product number we don't need to call it product ID go ahead and finish and now you can see that drop down and if I go into view and form view I can actually click this drop-down and select anything I want there and you'll see the way that works we can redo this so that it looks nicer just simply by going into layout view and deleting this item clicking the drop down list again going through the exact same steps I'm going to pull it out of a table it's in the customers table and I want to use customer ID company name and also state we sorted this first by state and then by the company name and there we go with the dropdowns and I want to store the value in customer ID and we're going to go ahead and customer ID there for the label and finish and now I have a drop-down list for that so again when I come back in here to form view I can click this drop down and I can choose a value from that list

Channel: learnmsaccess

Views: 397,660

Rating: 4.7301588 out of 5

Keywords: Microsoft, Access, 2010

Id: UK9Pumxw4sQ

Length: 10min 13sec (613 seconds)

Published: Sat Nov 13 2010