The CAT curriculum includes using a finite Value List to create a Combo Box field in an Access database table. This is appropriate where your list of options is finite, for example, a list of grades in a senior school which will always be 8, 9, 10, 11 & 12.
When the Value List needs to be more flexible the unique values should be stored in a separate table which can then be linked to the table in which the Combo Box field appears.
In a way a lookup table is similar to a VLOOKUP in Excel.
Let us consider a database in which there is a table with details of learners in a school named tblLearners. For transport purposes, we need to record the suburb in which each learner resides in a Suburb field in tblLearners. To ensure accurate and quick data capture we use a Combo Box field type. Rather than enter a list of the names of the suburbs as a Value List for the field, we do the following:
- Create a table called tblSuburbs with 2 fields:
- suburbID (Autonumber, Primary Key)
- suburbName (Short Text, Required=Yes, Indexed=Yes(No Duplicates))
- Switch to Datasheet View and add some suburb names to your table
- Save and close tblSuburbs
- Open tblLearners in Design View and set the Suburb field to Number
- On the Lookup tab for the Suburb field, set the Display Control to Combo Box
- Set the Row Source Type to Table/Query
- Select the lookup table, tblSuburbs, from the list as your Row Source
- If you switch to Datasheet View you will now see a Combo Box list in your Suburb field, but it is populated with the suburbID, not the suburbName. To correct this, switch back to Design View, select the Lookup tab for the Suburb field, and set the Column Count property to 2
- Switch back to Datasheet View and test your Suburb field now
When you need to add, edit or delete suburbs in your database, you will open your tblSuburbs table in Datasheet View and not work in Design View of your tblLearners table. By adding data to (or editing data in) the tblSuburbs table you are effectively updating the list of lookup values for the Suburb field in tblLearners.
Your next step in mastering Microsoft Access could be: Creating a relationship between 2 tables in Microsoft Access