Home and Learn: Microsoft Access Course

Access Dropdown Lists

In the previous lesson we create 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:

An arrow pointing to the Book Genre field in the new Access table

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:

An arrow pointing to the edge of a field name so that you can widen the columns

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:

Science Fiction
Food and Drink

Your BookGenres table will then look like this:

The Book Genres field with data entered

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:

Access Design View with the Genre field 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:

Access Data Types showing the Lookup Wizard highlighted

When you click on Lookup Wizard, you'll see this dialog box appear:

The first screen of the Lookup Wizard in Microsoft Access

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:

Selecting the tables or queries for the Lookup Wizard in Microsoft Access

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:

The Book Genres field select for the Access dropdown list

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:

The sorting options for your list

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:

An Access column select for an Ascending sort

Click Next to move on. When you do, you'll see the items you selected for your list:

The Access Lookup Wizard showing what items will appear on the dropdown list

Click Next to see the final screen:

The final screen of the Access Lookup Wizard

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:

A field in Access showing a dropdown list of values to choose from

(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.

<--Back to the Microsoft ACCESS Contents Page

Email us: enquiry at homeandlearn.co.uk