Home  Fees/Services  Access Templates  Tutorials  Tutorial Downloads  Articles  Search  Contact  Privacy  Links

Efficient Quick Lookup List
Access Combo Box

Updated On 4/21/2017 5:28:15 PM

Combo Box Tutorial

Combo Box Tutorials
Fill Form Field Code
Combo Box: All/Any
Dependent DropDown
Cascading Combo Box
ComboBox Multiple Items
Lookup List Best Way
Lookup Lists 1 Table
Faster Combo Boxes
Table-based Combos
Y/N Drop Down List

Fast Lookup Lists

Store Text Data As Integer Pointers to Look Up List Tables

Now that you've seen how not to do it let's see the correct way to setup lookup lists.
Here's the new table design again: 

Lookup List Boxes Proper Setup

Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.

Are you looking for an MS Access template database? We have many for sale in our Access Template and Examples Software section.

We add an Autonumber ID field to the table.  The value of the field will go into our data tables rather than the actual text value of the shipping method.

Make sure the ID field is indexed (no dups) and that it is a Long Integer.  The ID field will act as an efficient pointer into our lookup list.

Using the ID field will make the database compact, efficient, more reliable.  It will also eliminate the need for Cascading Updates to the shipping method - an example is when I go back and fix the missing 'e' on service in the list in example two.  If you used example 2) and you had a million shipping records then the database would need to analyze each record and update the spelling changes in hundreds of thousands of records.

Additionally, this method of creating lookup lists reduces the need to associate an abbreviation or code for each entry.  For example: F for Fedex; A for Airborne; J for Jim's truck, etc.

Alternatively, you may have the autonumber ID field set to the primary key of the table.  In this case don't forget to set the text field to indexed no duplicates - many believe that this is the preferred method - we don't disagree.

One more thing:  If you ever need to increase the size of the shipping method field you can do it without affecting other tables in your database.

Here is the new combo box on the form (with property sheet):

access lookup lists

A Blue Claw Database Design Article:

Repair Corrupted Access Table

Blue Claw Database Design Downloadable Tutorial:
TransferText & OutputTo Microsoft Access Download.  

A Blue Claw Software Design Template:

Work Order Administration Software

Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, Access 2010, MS Access 2013