Microsoft Access 2007 2010 2013 pt 7.a (Auto-complete Form fields)

hello everyone in this video I just wanted to quickly talk about how to create what is known as autocomplete feel so when you pick something it automatically fills up some of the information which is underneath it so say for example I pick the product ID then when I pick it it should fill in the price of the product rather than I having to put the product high price in there and somebody asked me a question about it and I thought I'll just make a quick video this won't be a long one and it's very interesting I had been thinking about looking at it and learning it so I learned in the process too so I'm still looking at my previous example like that I used in the video number 6 I believe with the customer ID and products so I'm going to continue with that and for these things to work for the autocomplete you always trying to have relationships so you've got to have the relationship because you always pulling up information from some other tables or something so so you got to have the relationship first so you know that you can create relationship from here you can left-click and hold it and drag it and drop it here that's one way of doing it and I'm just going to close this and the other way we looked at was when you are in the design view of the table and you can start the lookup wizard and then you use the first option that I want to look up a field in under the table so you'll find this in my relationship video and in that you know when you do that you also get to pick the fields you wanted to be looking up it's gonna cancel this I'm gonna open up my table order details table here so you see when I click in the drop-down it finds this information and this Product ID is coming from my table products so I have this table products which has the product ID product name and then the unit price so what I had done when I did the lookup wizard I pulled up the product ID the name of the product and also the price but I did not want the product ID one two three listed here I just wanted the name the product there but there are three columns here you just said the first column I am hiding it okay so that's very important because I've got one two three columns the first column is hidden so in access the first column is considered zero this column is considered one and then the price column will be considered two and this is important because when we pull the information which we're going to do in the form it needs to know which column it needs to pull the information from so I'm just going to close all of these and what I'm going to do is quickly is I'm going to create a form for the table or the details so I can go to create and I've got the form wizard now for 2007 remember the form wizard is under more forms you see form wizard here so I click it that is listed here so you see I've got Product ID here and also the unit price so I'm going to add the fields to my form next next and it's always good to give the name frm for form in front of it and I can click finish okay so now when I go to new record what I would like to do is that when I pick a product the unit price comes up here automatically alright so let's look at it so the to do this we can go to the design view and before we go to the design you also wanted to mention one thing that I didn't clearly mention and I was gonna answer that properly in the layout view when I try to change the layout see see in this one I can change it so sometimes you find that you cannot change it because all of these are bound to each other so if that happens you just right-click on any one of these fields and then you can use the anchoring option to move it around and when you right-click you can also use the layout option and use the option that says remove layout you see if it is tabular I think they all are joined together so any fields you wanted to control you couldn't control just use the layout option remove the layout now you'll be able to move it around and you could always add new fields that you didn't have in here and you can use these options here if you the add existing fields so if it's not there you can always choose it and then just drag them there and you can also go to show all tables and then you can try to find the fields you want and you can just drag it in here and then you can layer it okay so to do the autocomplete let's go to the design view okay and I'm going to click on this product ID and you can right click and go to properties or you can just go to this property sheet here on the top and this is where you just want to look at a few things button is the this name which is the product ID because this is something we'll be using and I'm going to click on this unit price and I can see the name of this unit price is unit price and that's why you see naming conventions are good being a good habit to be consistent so that you don't make spelling mistakes and uppercase and lowercase so I want the price to show up from whenever somebody chooses a product ID so I click on product ID and I go to this option called event and I click in the section called after update there's a third row there and I click on the dots at the end and I choose cord builder and I click ok so when you click on the code builder you'll get this and this is where you get into some of the programming thing and what I want to do is I just want to put in a simple code so I just typed it in here we're just ignore the ones on the top just look at this one I'm just going to highlight and copy it and I'm gonna explain to you here and go to this guy here and I'm gonna paste it so what I'm saying is unit price is equal to product ID so these are the names of the fields dot column two so if he as I had mentioned there are three columns in that product ID the first column is not visible which is just the number one three so because if I choose one it will only put matrix in the name of the book in there but I want the price I've got to make sure I put column number two in there I can now save it and I close this now I can hit save here too and I'm going to switch over and I'm going to click on a new records on the bottom there's that arrow with the yellow symbol and let's see so if I click here and I choose say my book you see the price forty five shows up there now if I want I can go back to my design view or layout view and do the subtotal calculation too if I wanted so just to do that quickly if I want I can go to the design view and I click in the box for the subtotal and I'm just going to put equal quantity because that's the name of the field multiplied by unit price and I hit save and I switch over to the form view let's see if I go to a new record and I choose a product and I choose a quantity say two and I hit tab and there is my subtotal so I just thought that I wanted to show this quickly because this is a nice little feature that can be used to fill things up and usually you cannot do this in the table this is always done in the form and you can also have sub forms like form on the bottom and bottom on the on the top in the bottom and you can fill information from different places thanks for watching hope this was useful

Channel: Amir Parmar

Views: 159,241

Rating: 4.7762237 out of 5

Keywords: Microsoft access autocomplete, access autocomplete, access form auto complete

Id: TCaZ7AFZjpY

Length: 8min 15sec (495 seconds)

Published: Thu Jun 20 2013