Adding values to lookup tables

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

In Access 2007, 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

To use the new properties in Access 2007:

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