The Access data lookup fields provide users with a drop-down menu of approved options to choose from when entering tabular data. With this video tutorial, learn how to implement lookup fields and create a lookup list.
- [Instructor] A look up field allows you to select a value from a list when populating data with information. Like validation rules and default values this is another tool that you can use to ensure that the information in your access database is valid and consistently entered. I went ahead and added several new tables into our database here to support the hotel. We have several tables about the room assignments for our guests, as well as details about the rooms themselves. We also have a table about Landon employees. I'll go ahead and double click on it to open it up in data sheet view. Now the Landon Hotel has properties in several different countries.
So when it comes time to adding a new employee to the database, I wanna make sure that they get assigned to an appropriate country location. We can control this data entry with a look up list. Let's go ahead and switch our table into design view. I'll just right click on the tab name and switch it there. Then we're gonna focus on the country fields. The look up list can be accessed through the data type. So we're, instead of short text right now, I'm gonna sue the drop down menu and choose lookup wizard. The wizard starts by asking us where we want to get the values that'll appear in our list. We can either pull the values from another table or a query, or we can type in the values that we want.
I'm just gonna create a short list. I'll just go ahead and type in the values that we're going to use. I'll choose that option and press next. Then, we'll create a small table that includes the values we want included in our look up list. These will be the countries of United Kingdom, United States, India, and Ecuador. When you get done typing those in, press the next button. At the top of this screen, we can change the name of the column if we'd like. I'm just gonna go ahead and leave it saying Country.
We also have an option to limit to list. If we leave this off, then we can choose from the list or we can type in whatever we want. I'm gonna turn this option on. This will prevent other values from getting entered in. And the user can only choose the options that are presented on the list. We can also choose to store multiple values with this check box here. But that doesn't really make sense in this case because an employee can't be in two different countries at the same time. I'll go ahead and leave that off. We're done with the wizard so I'll press the finish button and that'll get changed into our database.
Now at this point it doesn't actually look like anything has changed here in our field properties. And the data type is still set to short text. That's because the data that's getting stored is the short text data type. What did change though is available here in the look up tab of field properties. Let's go ahead and switch over to that and we see all the values that were entered though the wizard. The display control is going to be a combo box. And that's what gives us the drop down menu effect. The row source type is going to be a value list. And the row source is gonna be the values that we typed in, into that table. We can see that the limit to list property was set to yes and the allow multiple values was set to no.
There are lots of other options here that describe how many items adhere the list before scrolling and how wide the drop down menu is and so on. Let's go ahead and leave everything as is. And we'll switch into data sheet view to see everything at work. Now once again, we've made a structural change to the functionality of this database table. So let's go ahead and save the changes and that'll then go ahead and switch our view. Now, I'll go over to one of our countries and when I click on it, you'll notice we now have a drop down menu where we can make a selection. I can make a change based off those selections but if I try to type in something else, for instance I'll type in Mexico, when I press enter it's telling me that the text that I entered isn't an item on the list.
I need to make sure that I'm choosing from the list only. Press okay and then make a different change. One thing I'll point out here is sometimes you can get locked into a loop of being able to enter in a valued entry. For instance, I'm gonna type in Mexico once again. I'm gonna bring up that error message once again and press okay. At this point it wants me to make a change but if I don't remember what the original value was, what I can do instead is press the escape key on my keyboard. And that'll actually just return me to the last valid state of that data. In addition to providing control over what gets entered, look up lists can dramatically speed up data entry as well.
When choosing from a list of acceptable values. You can simply type in the first few characters to select that matching option.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.