Home and Learn: Microsoft Access Course
Access Dropdown Lists
In the previous lesson we created a new table to hold all the books that an author has written. We created a book Genre field. In this lesson, you'll learn how to add a dropdown list to an Access field. That way, instead of typing a genre, you'll be able to select one from a list.
Click on the Create Ribbon again and create a new table, just like you did before. Rename the table and call it tblGenres. Stay in Datasheet View for this one. Click to Add a new field. Give it the name BookGenres. Set the data type to Short Text. Your Access screen should look like this:
The image above can be confusing. That's because three tables are open: tblAuthors, tblBooks and tblGenres. The table you're working on will be highlighted in a reddish color. You can close down tables, if you want. Then just double-click a table in the All Access Objects are on the left to open them up again.
Now we'll enter some genres. You type your data into the cells, where it says Type Here in the image above. When you've typed one, you can either hit the enter key on your keyboard or press the TAB key. Hit the TAB key again to move into the next BookGenres cell. Or you can hit the down arrow on your keyboard. (The ID key will take care of itself, so you don't have to enter anything in there.)
If your cells are too small, you can widen the entire BookGenres field. Move your mouse between the headers of BookGenres and Click to Add:
Your mouse pointer will change to a left and right arrow. When it does, hold your left mouse button down and drag to the right. The field will resize.
Enter the following in the cells under BookGenres:
Crime
Thriller
Science Fiction
Romance
Travel
History
Science
Fantasy
Food and Drink
Biography
Self-Help
Your BookGenres table will then look like this:
Close this table down by clicking the X in the tblGenres tab at the top. If your get a message asking if you want to save the changes to the layout of tblGenres, says Yes.
Now you need to switch back to your tblBooks table. If you're not already, go into Design View (Fields ribbon > Views panel > View.) Click into the Genre field so that it's highlighted:
Note that the Data Type is set to Number. We need to change this. Click into the Data Type cell, where it says Number. From the dropdown list that appears, select the Lookup Wizard item:
When you click on Lookup Wizard, you'll see this dialog box appear:
The correct option in the Lookup Wizard is already selected: we want to get values in our dropdown list from another table, which will be our Genres table. So click Next to see this screen:
On this screen, you select the tables or queries you want for your dropdown list. The values we want are in the tblGenres. Select this table and click Next. You'll move on to this screen:
This screen is asking which field or fields do you want from your selected table or query. On the left of the screen, you'll see all the fields you set up in your table. We only have two. The one we want is the BookGenres field. Select that field and click the right-pointing arrow. This will add the field to the box on the right, Selected Fields:
You can select more than one field for your dropdown list. For example, you may have first name and last name fields. You could add both to the box on the right and create a dropdown with the full name in it.
But ours is OK with just the BookGenres selected. Click Next to see this:
This is where you can add a sorted dropdown list. If you're not bothered about a sorted list, just click next. But click the dropdown to the right of the number 1 above. Select the BookGenres item:
Click Next to move on. When you do, you'll see the items you selected for your list:
Click Next to see the final screen:
The default name at the top is fine. But feel free to call it something else. We're not bothered about Data Integrity and we haven't got multiple values, just the one field. Click Finish to exit the Lookup Wizard. You many get a message about saving the table. If so, click Yes.
If you're in Design View, it may look as though nothing has happened. But switch back to Datasheet View (Fields ribbon, remember, then Views, View menu.) Click inside of your Genre field and you'll see it's now a dropdown list:
(If the items in your list are too small, you can widen the column, like you did before.)
When we come to data entry, having a dropdown list like this will save us a lot of time. We can just enter a book title and select a genre from the list.
OK, we're looking good. The next step is to link the two tables together, the books and authors table. To do this, you need to know something about primary and foreign keys. If you know all about Primary and Foreign Keys, there's a link below to skip the next lesson.
<-- Previous Lesson: Access Design View | Next Lesson: Primary Keys and Foreign keys -->
I'm all over Primary Keys. I'll skip it -->
<--Back to the Microsoft ACCESS Contents Page