Access 2010: Auto populate fields using a combo box in forms.

a good afternoon here's what we're going to do we have a table that I created called TBL training no information it yet so we want to fill in my employee ID which will match back to employee ID in the employees table so we'll have a one-to-one relationship between those two tables we're going to use a combo box in a form to populate or to pick the employee ID that we want from that it's going to auto fill in the next two fields first-name and lastname based on the first-name and lastname associated with the employee ID and then we'll fill in the other data as we go through and fill in the form so we need a form to do that so the form is required to do the filling in that we're talking about so I'm going to have a combo box for employee ID which also shows first name and last name based on to the selection that you pick from the combo box it will fill in the content of the first-name and lastname in the form which again populates the table so first let's go this create a relationships in here so I'm in the database tools clicking on relationships and I get the employees table here I got the training table over here we've got an employee ID in both tables and so they are unfilled size and data type are the same in both tables so we're going to do a one to one relationship so I'm going to put my pointer on employee ID and employees table I'm going to click and hold drag over to the employees ID in the training table let go that brings up the Edit relationship window table on the left is the primary table table on the right you can see it says related here so the training table is a related table the employees table is a primary table primary table is where you have to put in the employee ID and you have to generate the employee ID put any information first name last name address all that stuff has to be done there first prior to doing anything in the training table so this that's why this is considered the primary table we're going to enforce referential integrity which means I can't put in an employee ID over here the training table that doesn't already exist over here the in the employees table so we're going to click on create right there and there's our one-to-one relationship we close out of the relationships window alright so we're going to create a form we selected the table called training and come up here and we're going to click on the create tab for forms I'm going to use the wizard there's a couple other options that you can pick but we'll just use the wizard it makes it pretty simple because they have training selected over here on the left it automatically selects it here inside the wizard I want all fields in the form so I hit the double greater-than symbol I'm going to click on next and we choose the column ER layout just pretty standard click on next the title for this particular form is frm training and I'll hit finish to complete it there it is and I could do my data entry have to manually type everything in right now so we need to change some of this so let's go back to the design view of the form and in here what we have to do is we have to replace the employee ID text box with a combo box once we do that by selecting the employee ID from the the combo box it will auto populate the first-name and lastname textboxes in the form and do that though we have to create the combo box the combo box has to also have the information that we want to use to populate the other text boxes so it also has to include first name and last name so I'm going to delete this first and we're going to put in a combo box so come up here to my design tools here's my combo box click on it and we're going to put it right about there we're using the first option to get our values from another table so we click on next we want to pull the data from the employees table so we click there and next we want the employee ID first name and last name those are the three we want click that next sort let's sort by last name not necessary obviously but we'll sort by last name and we also want to unhide the key count because we want in the form to display the actual employee ID number and then of course by selecting that it's going to information from the first name and last name so we'll click on next and we're going to store that information using the employ of the employee ID field and we're going to store that in the employee ID field in the training table we hit next and the name of this particular label will be emp1 gulps and then ID employee ID click on finish so there it is and I'll be right back okay that looks pretty good I'll lined up those are all the same all right so what we're going to do is take a look at it let's go back to the form view here's a drop-down box see there's all the information the employee ID first name and last name I pick one but nothing is filling in those fields yet that has to be done through writing code so I hit escape to cancel that close out of that well when done close we want to go to the design view all right so we need to write some code associated with the combo box for to do that we're going to name these in a more appropriate way because we're going to do some programming with them so this one is going to be called after you select that we go to the alt tab and it's a very very top see where's his name this is combo 11 so we're going to call the CBO for combo and see boem PID filed up by pressing the enter key do the same thing for because we're using it in code won't I want to identify this very specifically it's not just call it first name we'll call it txt for txt stands for this is a text box and in the text boxes for first name do the same thing for last name txt getting in the right spot txt last name hit enter there we go now if we were doing some programming code for the label we would label that you can see the name of the label is for last name underscore label you know again a lot of the standard is LBL for labels LBO last name again using camel case so the capital L capital end as well the first three identifiers is all we are always a lowercase so when I do anything with label same ways we got two text boxes renamed and the combo box renamed so we use some code associated with the combo box that will populate those two fields so we're going to do is we're going to the event tab and we want to do is the code to be initiated at the time that the content changes so when you hit the drop-down box and select some employee ID that's a change so soon as that makes a change it will run the code associated with it and that will then fill in these two fields so I'm go over here horses on change click there then there's a three dot ellipsis button click on that and we use the code builder which brings up the Visual Basic for application interface there it is and right here is in the middle of this private sub and end sub is this called subroutines is where we're going to write this so when it when you actually click on the drop-down pick an employee ID number then that's changing the contents of that field and that will run this code so we basically want to set the content of the the two text boxes first name last name to the first name and last name associated with the employee ID that was selected so we're using me dot me just means the form we're currently in me dot txt and there it is first-name lastname well you start off with first name dot value is the where the data is stored in the text box equals now I don't have to write it all the way out as long as is highlighted and you can see right here how its highlighted and how it is highlighted so I'm going to press the equal sign and it fills it in too it's all filled in now again me got this case CBO employee ID is the combo by dot column now the column that the data in the first name is in the second column B there's three columns but they're not actually called counting one two three they're called columns zero one and two so first name is actually in column one so I select column I do my open parenthesis and it says index as long well so the index is the column number which happens to be column one long is a data type in this case it's an integer of type long so there it is I hit enter and it put some spaces in there capitalize the M&E so it looked like it recognizes it so the next code is going to be me dot txt last name dot value I spell it correctly all right equals me dot CBO dot palem open parenthesis to close parentheses hit enter and you can see it recognizes it as well so we're done we don't have to save or anything we just close out a Visual Basic for applications and there's the code is called an event procedure for the on change event so let's check it out so let's go to the form view we'll pick an item and then filled it in so I go to the next hit the new button and that saves it as soon as I hit the new button there another one and of course we can put in a date for the training value for the length of training let's say for hours okay there we go anyways we'll close out of this save that form we're going to open up the training table see if the data is in there double click there's our data that we just filled in so everything is working great all right no problems so that's the code again the code is associated with bring up the this and design view again the codes associated with the combo box in the event called on change click on the ellipsis button again here's the code taking the the txt first name textbox the value which is the is the content of what's going to be in that text box we're saying that equal to what's in column 1 of the employee ID combo box and then last name the value of last name is going to be associated with what's in column 2 of the employee ID combo box and a couple final words and those are and remember it in the table called training with this form is populating the data in that table remember the last name and first name fields are not required in this particular form or in the table because the employees table and the training table both have the employee ID and we have that relationship already created and established so anytime you want to pull information from the training table even if it didn't have first name your last name you can create a query to combine the information in the employees table with the information in the training table because of the relationship through employee ID to come up with all the different fields that you want to make visible so it's not required to have first name and last name but it's something that a lot of people prefer to have so again that's a personal preference so I hope you learn a lot and go ahead and start coding your own database

Channel: Allen Jarrett

Views: 510,669

Rating: 4.8815293 out of 5

Keywords: Access 2010, Access, VBA, Technology, Software Tutorial

Id: rSPS24EfijI

Length: 11min 56sec (716 seconds)

Published: Wed May 23 2012