Microsoft Access Tips for Serious Users

Provided by Allen Browne. Updated June 2006.

Adding values to lookup tables

Every database application uses combos for selecting a value from a lookup table.

In the newer versions, combos and list boxes have new properties to make it easy to add items to the list. (The old ways still work as well.)

Access 2007 and later

To use the new properties:

1. Open your form in design view.
2. Right-click the combo, and choose Properties.
3. On the Data tab of the Properties box, set Allow Value List Edits to Yes, and  List Items Edit Form to the name of the form to use for adding items to the list.

When you are using this form, you can now right-click the combo, and choose Edit List Items.

All versions

By setting the combo's LimitToList property to Yes, you can use the NotInList event to append a new entry to the lookup table.

If several fields are to be entered (e.g. adding a new client), open a data entry form in dialog mode:

    DoCmd.OpenForm "MyForm",,,, acAdd, acDialog

Dialog mode pauses your code until the entry is complete. You can then use acDataErrAdded to cause Access to find the new value.

In other situations only a single field is needed, such as a category, or a title like Mr/Ms/Dr/... Opening a form is unnecessary, as the user has already typed the new value. The function below verifies the entry and appends it to the lookup table.

This function identifies the lookup table from the combo's RowSource property. It assumes the field name in the lookup table is the same as the combo's ControlSource, i.e. the primary key name and foreign key name must be the same.

Follow these Steps:

1. Paste the function below into a general module. Save the module.
2. Verify the combo's LimitToList property is Yes.
3. In the NotInList property of your combo, choose [Event Procedure].
4. Click the "..." button so Access opens the code window.
5. Between Sub ... and End Sub, enter:
   Response = Append2Table(Me![MyCombo], NewData)
   replacing MyCombo with the name of your combo box.
6. Repeat steps 2 - 4 for other combos.

This function will not work with Access 2 without modification.

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose:   Append NotInList value to combo's recordset.
' Assumes:   ControlSource of combo has the same name as the foreign key field.
' Return:    acDataErrAdded if added, else acDataErrContinue
' Usage:     Add this line to the combo's NotInList event procedure:
'                Response = Append2Table(Me.MyCombo, NewData)
    Dim rst As DAO.Recordset
    Dim sMsg As String
    Dim vField As Variant      ' Name of the field to append to.

    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
        sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
        If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
            Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
            rst.AddNew
                rst(vField) = NewData
            rst.Update
            rst.Close
            Append2Table = acDataErrAdded
        End If
    End If

Exit_Append2Table:
    Set rst = Nothing
    Exit Function

Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
End Function