Creating a Simple Lookup List


It is interesting how many different ways people can come up with to enter the same items of information in a database. Asked to enter the name of their home state, for example, residents of the state of Washington will type Washington, Wash, or WA, plus various typographical errors and misspellings. If you ask a dozen sales clerks to enter the name of a specific product, customer, and shipper in an invoice, it is unlikely that all of them will type the same thing. In cases like this, in which the number of correct choices is limited (to actual product name, actual customer, and actual shipper), providing the option to choose the correct answer from a list will improve your database’s consistency.

Minor inconsistencies in the way data is entered might not be really important to someone who later reads the information and makes decisions. For example, Arizona and AZ refer to the same state. But a computer is very literal, and if you tell it to create a list so that you can send catalogs to everyone living in AZ, the computer won’t include anyone whose state is listed in the database as Arizona.

You can limit the options for entering information in a database in several ways:

  • For only two options, you can use a Boolean field represented by a check box. A check in the box indicates one choice, and no check indicates the other choice.

  • For several mutually exclusive options on a form, you can use option buttons to gather the required information.

  • For more than a few options, a combo box is a good way to go. When you click the arrow at the right end of a combo box, a list of choices is displayed. Depending on the properties associated with the combo box, if you don’t see the option you want, you might be able to type something else, adding your entry to the list of possible options displayed in the future.

  • For a short list of choices that won’t change often, you can have the combo box look up the options in a list that you provide. Although you can create a lookup list by hand, it is a lot easier to use the Lookup wizard.

In this exercise, you will use the Lookup wizard to create a list of months from which the user can choose.

Use the 05_SimpleLookup database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 05_SimpleLookup database. Then display the Field Property Test table in Design view.

1. Add a new field below LastName. Name it Month1, and set the data type to Lookup Wizard.

The Lookup wizard starts.

image from book

You can use the Lookup wizard to create a combo box that provides the entry for a text field. The combo box list can come from a table or query, or you can type the list in the wizard.

Tip 

If a field has a lot of potential entries, or if they will change often, you can link them to a table. (You might have to create a table expressly for this purpose.) If a field has only a few possible entries that won’t change, typing the list in the wizard is easier.

2. Select the I will type in the values that I want option, and then click Next.

3. Leave the number of columns set to 1, and click in the Col1 box.

4. Enter the 12 months of the year (January, February, and so on), pressing image from book after each one to move to a new row.

5. Click Next, and then click Finish.

6. In the Field Properties area, click the Lookup tab to view the Lookup information for the Month1 field.

image from book

The wizard entered this information, but you can easily figure out what you would have to enter to create a lookup list by hand.

7. Switch to Datasheet view, clicking Yes to save your changes.

8. Double-click the vertical bars between the column headers to adjust the column widths so that you can see all the fields.

Tip 

You can manually resize columns by dragging the vertical bars between the column headers.

9. Click in the Month1 field of a record, and then click the arrow that appears to display the list of options.

image from book

Notice the button below the Month1 options list. Clicking this button opens the Edit List Items dialog box. This feature is new with Access 2007. The database user can open the editor by clicking the button, or by entering text that is not in the list and answering Yes when asked whether she or he wants to edit the list. If you don’t want users to be able to edit the list, you can disable this property, as we do later in this exercise.

10. If you opened it, close the Edit List Items dialog box and then click February to enter it in the field.

11. Click in the next Month1 field, type Jan, and press image from book.

As soon as you type the J, the combo box displays January. If you had typed Ju, the combo box would have displayed June.

12. In the next Month1 field, type jly, and press image from book.

Even though the entry isn’t in the list, it is accepted just as you typed it. Although there might be times when you want to allow the entry of information other than the items in the list, this isn’t one of those times, so you need to change the field properties to limit what can be entered.

13. Return to Design view.

The Limit To List property on the Lookup tab for Month1 is currently set to No, which allows people to enter information that isn’t in the list.

14. Change Limit To List to Yes.

15. Change Allow Value List Edits to No.

16. Save the table, return to Datasheet view, type jly in a new Month1 field, and then press image from book.

Access informs you that the text you entered is not in the list, and refuses the entry.

17. In the Microsoft Office Access message box, click OK.

18. In the Month1 list, click July.

The month of July is displayed in the field.

Close the 05_SimpleLookup database, saving your changes.



2007 Microsoft Office System Step by Step
2007 MicrosoftВ® Office System Step by Step
ISBN: 0735622787
EAN: 2147483647
Year: 2004
Pages: 231

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net