Creating lookup fields

- A lookup field allows you to select a value from a list when populating a table 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. Now you notice here inside of the LandonHotel-Lookup:Database, I've imported a couple of more tables that will support the hotel's operations. One of them, is the LandonEmployees table. If we double click on it, we can take a look at it. Inside of it, I've got some information about the names of the members of our executive team, as well as their titles.

I've got address, the country where they're based, their phone number, email and a long bio right over here. Now the way the hotel has properties in several different countries, so when it comes time to adding a new employee to the database, I want to make sure that they get assigned to an appropriate location. We can do that by creating a Lookup field on the Country here. So we'll switch into design view in order to add it. I'm going to come down here and select the Country field, and right now the data type is listed as Short Text. If I click here, I'll get the drop-down list where we can change it. And from the menu, I'm going to choose Lookup Wizard at the very bottom.

That will start up the Lookup Wizard, where we have a couple of options. The first option is to have the Lookup field values come from an existing table or query. So if we had a listing or another table over here of the different countries, we could use that. I'm going to choose this option here, to type in the values that I want. In which case, when I press Next, I'll just fill in this little table here with the appropriate values. So the first one, is going to be United Kingdom. The next one, is going to be United States, followed by India and Ecuador. After typing in those values, I'll press the Next button here.

And we get to label our new field, in which case, it's just going to pull out the name of the original field which is Country, so I'll just leave it at that. There's a checkbox right here that says Limit to List. If I leave this unchecked, Access will let me enter in whatever I want in the list, even though it will give me some options with the drop-down menu. If I turn on Limit to List though, I can only choose from the options that are available in the drop-down list. I won't be able to enter in anything else. So in this case, I'm going to turn on Limit to List, to make sure that I only select from those four options that we just typed in. And we'll go ahead and press the Finish button here.

Now at first it might look like nothing has changed. My data type, you'll see, is still listed as Short Text. And that's because the value that we're storing here, in fact, is the Short Text data type. What did change though is down here, in the Field Properties, you'll notice we have two tabs. One of them is General and then we have Lookup. When we switch over to the Lookup tab, you'll see some information here. The Display Control is now listed as a Combo Box. The Row Source Type is a Value List. And then the Row Source items are the listed items that we typed in a moment ago. So we see United Kingdom wrapped in quotation marks and then a semi-colon.

Then United States in quotes with a semi-colon. And India and Ecuador also wrapped in quotation marks. And all of those are separated by semi-colons. So that's the listing that's going to appear in our drop-down menu here. And then we have some additional information that control how the list works. So at this point, we can go ahead and close and save our table. I'll switch to data sheet view. And I'll accept this button here to save the changes. And now if I go over here to one of our Country fields, let me just double click here to expand that column a little bit. When I click on the first country, you notice I get a drop-down menu now.

And if I open that up, I can make a selection from these four items right here. So I can switch it to India or I can switch it to United States. If I type in another value, perhaps I'll type in China and then when I press Tab to move out of that field, I'll get this warning message here saying that the text you entered isn't an item in the list. And that's because we checked on that option to limit the valid entries to the ones on the list. Let's go ahead and say OK to this and we'll have to make a change here. I'll just change it back to United Kingdom. In which case, that does become a valid entry so I can move out of that cell. So in addition to providing control over what gets entered, Lookup 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 the matching option.