Using the Lookup Wizard to Create a Drop-down List

Locked lesson.

Upgrade

About this lesson

Control the information entered into a field by creating a drop-down list using the Lookup Wizard. 

Quick reference

Using the Lookup Wizard to Create a Drop-down List

In this lesson, we will add the Priority field to the table and control the information that can be input by creating a drop-down list using the Lookup Wizard. 

When to use

Use the Lookup Wizard whenever you want to control the information users can enter into a field.  

Instructions

The next field to add to the table is 'Priority'. The priority for this ticket is 'Low'. We are going to create a combo box so users can select a priority from a drop-down list of items. 

We can create a drop-down list using the Lookup Wizard which will guide us through the process. 

Creating a Drop-down List Using the Lookup Wizard

  • From the Home tab, click Views and select Design View. 
  • Add a new field for 'Priority'. 
  • Click in the Data Type field and select Lookup Wizard from the list. 

First, how do we want the lookup field to get its values? Do we want to get the values from another table or query or do we want to type the values?

  • Select I will type in the values that I want. 
  • Click Next.

Now, let's choose the number of columns for our list. We only need one column to list the activity levels. 

  • In the Number of columns box, type 1. 
  • Type the list values, 'Low', 'Medium', and 'High'. 
  • Click Next. 

Next, what label do we want to use for the lookup field? By default, the field name will be used. 

  • Type 'Priority' as the lookup field label. 

Do we want users to be able to type other priorities outside of the list items? No, we don't. Users should only be allowed to select values from the drop-down list. 

  • Check the box, Limit to List. 

Now, do we want to allow users to select multiple values from the list? No, we don't. They are limited to only one selection. Leave the checkbox de-selected. 

  • Click Finish. 

Finally, add a description for the field. 

  • Click in Description and type 'Priority for this ticket'. 
Defining the Lookup Field Properties

Now we have created our drop-down list, we need to define the properties for this field. 

  • Change Field Size to 40 chars. 
  • Change Required to Yes. 
  • Change Allow Zero Length to No. 
  • Change Indexed to Yes (Duplicates OK). 

As this field is set up as a lookup, we have an additional tab called 'Lookup' where we can define the properties specifically for the drop-down list. 

  • Click on the Lookup tab in the Field Properties area. 
  • Review the settings. 

Display control defines the type of lookup. In our example, we are using a Combo Box. 

The Row Source Type defines where we are getting the values that populate the list from. We created our own list by typing the values. 

The Row Source lists the values in the drop-down list. We can manually modify these and even add extra items to the list by modifying the text. Each value must be contained within quote marks ("") and separated with a comma (,). 

  • Click in Row Source and add the value "Low - Medium". 
  • Change Limit to List to Yes. 
  • Change Allow Value List Edits to No. 

Adding a Priority to Each Record

Now we've created a new field and set the properties, we can go back to our records and add the priority information for our ticket. 

  • From the Home tab, click Views and select Datasheet View. 
  • If prompted to Save, click Yes. 

At this point, Access might pop up a data integrity warning message. In general, we only need to check this once we have finished creating our database. 

  • Click Yes on the message. 
  • Click Cancel on the next message. 

Notice we now have a new column called 'Priority'. 

  • Click in the Priority field for the first ticket and select Low from the drop-down list. 

Adding More Fields

Let's add two more fields to the table.  

  • From the Home tab, click Views and select Design View. 
  • Add a new field for 'Technician'. 
  • Click in the Data Type field and select Short Text from the list. 
  • Add a description. 
  • Add a new field for 'Description'. 
  • Click in the Data Type field and select Long Text from the list. 
  • Add a description. 
Defining the Lookup Field Properties
  • Select the 'Technician' field. 
  • Change Field Size to 40 chars. 
  • Change Required to Yes. 
  • Change Allow Zero Length to No. 
  • Change Indexed to Yes (Duplicates OK). 
  • Select the 'Description' field. 
  • Change Required to Yes. 
  • Change Allow Zero Length to No. 
  • Change Indexed to No. 

In general, long text fields are not indexed. 

Login to download
  • 00:04 In this lesson, we're going to add more fields to our table.
  • 00:07 And I'm going to show you how you can create a drop down
  • 00:11 list to select a value from using the lookup wizard.
  • 00:14 So let's refer back to our notepad file.
  • 00:17 So if we look at our first ticket here, I can see that this has a priority of low.
  • 00:23 So what I really want to be able to do with this table is,
  • 00:27 I want users to be able to select a priority from a drop-down list.
  • 00:31 So maybe low, medium, or high.
  • 00:34 So let's take a look at how we would do that.
  • 00:36 So the first thing I'm going to do here is just jump back into design view so
  • 00:40 that we can add another field to our table.
  • 00:43 So this field is going to be for the priority.
  • 00:46 Now, when it comes to the data type, because we want to create a drop down
  • 00:51 list using the lookup wizard, we select the last option in this list.
  • 00:56 And this is going to open up that lookup wizard.
  • 00:59 And if you're not familiar with wizards,
  • 01:01 you will come across them in many different Microsoft applications.
  • 01:04 They basically help guide you through the process of creating, in this case,
  • 01:08 a drop down list.
  • 01:09 So the first thing the wizard is asking us for, it needs to create a lookup
  • 01:13 field which displays a list of values that you can choose from.
  • 01:16 How do you want your lookup field to get its values?
  • 01:19 So, do I want the lookup field to get its values from another table or
  • 01:22 query or do I want to type the values in myself?
  • 01:25 Well, as I said, this is going to be for the priority and
  • 01:28 I want to be able to specify low, medium, or high.
  • 01:31 Now, I don't have that information stored off in another table, so
  • 01:35 I'm going to type in the values that I want and click on next.
  • 01:39 Now, I can type those values in.
  • 01:41 So we're going to have low, Medium, Or high.
  • 01:50 Let's click on next.
  • 01:52 Now I get to define the label for my lookup field.
  • 01:55 It's already taking the name of the field as the label.
  • 01:58 I'm happy to leave it on that.
  • 02:00 And then I can choose, if I want to, limit the entries just to those three choices.
  • 02:05 So if I don't want people to be able to go in and add to the list that I've created,
  • 02:10 then I would select Limit To list underneath.
  • 02:13 And in this stage, I'm not going to allow them to select more than one value.
  • 02:17 So, let's leave the allow multiple values checkbox empty, and click on Finish.
  • 02:23 Let's add in a description for this field.
  • 02:26 So, now let's take a look at the field properties.
  • 02:29 So let's select priority.
  • 02:31 going to go down to field size first of all.
  • 02:34 Now 250 characters is way too large.
  • 02:38 I'm going to allow, I'm going to say 40 characters for this.
  • 02:42 Is this field required?
  • 02:44 Well, yes it is.
  • 02:45 We always need to have a priority in the ticket.
  • 02:48 Are we going to allow zero length?
  • 02:50 No, we must have a priority.
  • 02:53 Is this field going to be indexed?
  • 02:55 Do I ever want to search by tickets according to their priority?
  • 02:58 Well, yes, I could very well do that.
  • 03:01 So, I'm going to index this field.
  • 03:03 Now when you have a field that uses a drop down list what you'll also notice in
  • 03:08 the Field Properties area is that you'll have another tab called lookup.
  • 03:12 And this allows you to make small changes to that drop down list.
  • 03:17 So you can see at the top here it says Display Control combo box.
  • 03:20 So that is just the drop down that it's using.
  • 03:23 The row source is a value list so they're values that I've typed and
  • 03:28 then underneath in row source it says low, medium or high.
  • 03:32 And what I can do is, if I decided that I wanted to add another value
  • 03:37 into this list, I can simply edit the field property down here.
  • 03:42 So, I'm going to click after low, let's put in a space, and
  • 03:46 I'm going to add another priority of low to medium.
  • 03:50 And that needs to go in quote marks.
  • 03:53 And each value needs to be separated with a comma.
  • 03:56 Notice underneath limit to list is set to yes.
  • 04:00 I already defined that in the wizard, and I'm not allowing multiple values.
  • 04:06 Now, currently it says, allow value list edits.
  • 04:09 Now, I'm going to set this to no.
  • 04:11 I don't want anybody changing anything in that dropdown list.
  • 04:15 So now that we've set up that field, let's go back into data sheet view.
  • 04:19 Yes, I want to save the table.
  • 04:21 Now, what you'll notice is that when you start to make changes like this,
  • 04:25 it's going to pop up a warning message about data integrity.
  • 04:28 Now don't worry too much about this.
  • 04:29 We're going to click yes, first of all, and then on the next message that pops up,
  • 04:33 we're just going to cancel.
  • 04:35 Now notice that we have another column now for the priority.
  • 04:38 And if I click in this column,
  • 04:40 I get a drop down list that contains all of those values that I specified.
  • 04:44 So, for this first record, let's refer back to our notepad file because I can't
  • 04:49 remember what the priority was.
  • 04:51 Low priority for this particular ticket.
  • 04:54 So for this first one, we going to choose low.
  • 04:57 Now I think whilst I'm here, I'm just going to add in a second ticket.
  • 05:01 So let's say ticket number, customer name,
  • 05:08 And the priority of this ticket is medium.
  • 05:12 And just to finish off this lesson,
  • 05:14 let's add in a couple more fields in design view.
  • 05:17 So the next field name is going to be technician.
  • 05:21 This is going to be a short text field and
  • 05:23 the description name of technician that the ticket is assigned to.
  • 05:28 Let's check out the field properties in the General tab.
  • 05:31 Well, again, I don't think I need 255.
  • 05:34 I'm going to allow 40 characters.
  • 05:36 Yes, it's required.
  • 05:38 No, we're not allowing zero length.
  • 05:40 And yes, this field is indexed.
  • 05:42 Let's add the final field for this lesson and that is Description.
  • 05:48 So this field is basically going to house the description of the problem that this
  • 05:52 customer is having.
  • 05:53 So that is going to be a long text field because that description might be one
  • 05:58 paragraph, two paragraphs, three paragraphs long.
  • 06:02 So we want to make sure the data type we're using here is long text.
  • 06:07 And our description is, description of the problem.
  • 06:13 Let's check our fields.
  • 06:15 So, is this required?
  • 06:17 Yes, it is.
  • 06:18 Are we going to allow zero length?
  • 06:20 No, we're not.
  • 06:21 Are we going to index this field?
  • 06:23 Now, this is one of the only fields where I wouldn't apply indexing.
  • 06:27 And that is because, a description could be very, very long.
  • 06:30 It could be multiple paragraphs.
  • 06:33 Is it very likely that someone is going to search on a ticket using the description?
  • 06:38 Well, no, they're most likely going to use another field.
  • 06:41 Something like the ticket number, or
  • 06:43 maybe they're going to search all high priority calls, things like that.
  • 06:46 So I'm going to leave indexed as no for this field.

Lesson notes are only available for subscribers.

Gift this course