We're going to skip over most of the creation of the Access database. If you need to know more about databases, I recommend that you try About.Com Databases. The completed database, however, is shown in the relationship diagram on the left.

TopicTable is the "lookup table" and the main ActivityTable simply refers to the values in TopicTable using the key of TopicTable as a foreign key. The easiest way to build this relationship in Access is to use the Lookup Wizard. This is the last data type in the Design dialog in Access.

This wizard fills in the Row Source property:

SELECT [TopicTable].[TopicKey], [TopicTable].[TopicName] FROM TopicTable;

The key has to be selected along with the TopicName field to make the database update automatically. To keep the information from displaying on the form, the Lookup Wizard uses a sneaky trick. The Column Width property of the first column is set to 0".

Essentially the same VBA coding is used with our new database design, but this time, TopicTable is updated instead.

Private Sub TopicKey_NotInList( _
    NewData As String, _
    Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
On Error GoTo Err_Topic_NotInList
        ' Open a recordset using the
        ' TopicTable table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset( _
            "TopicTable", _
            dbOpenDynaset)
        ' Create a new record.
        Rs.AddNew
        ' Assign the NewData argument
        ' to the TopicName field.
        Rs![TopicName] = NewData
        ' Save the record.
        Rs.Update
        ' Set Response argument to indicate
        ' that new data is being added.
        Response = acDataErrAdded
Exit_Topic_NotInList:
       Exit Sub
Err_Topic_NotInList:
       ' An unexpected error occurred,
       ' display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress 
       ' an error message and undo changes.
       Response = acDataErrContinue
End Sub

Since the rest of the form works quite normally, this technique expands the usefulness of the lookup table a lot and works quite transparently! The completed form looks like this:

First page > Barb Describes Her Problem > Page 1, 2, 3