Tips for Serious Users

Provided by Allen Browne, March 2008. Updated April 2010.  (Replaces this older version of the article, which works in Access 95 or 97.)

Adding values to lookup tables

Combo boxes give quick and accurate data entry:

• accurate: you select an item from the list;
• quick: a couple of keystrokes is often enough to select an item.

But how do you manage the items in the list? Access gives several options.

Option 1: Not In List event

When you enter something that is not in the combo's list, its Not In List event fires. Use this event to add the new item to the RowSource table.

This solution is best for simple lists where there is only one field, such as choosing a category. You must set the combo's Limit To List property to Yes, or the Limit To List event won't fire.

In the Northwind sample database, the Products form has a CategoryID combo. This example shows how to add a new category by typing one that does not already exist in the combo:

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
    Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new product category?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO Categories ( CategoryName ) " & _
            "SELECT """ & NewData & """ AS CategoryName;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

Not In List has some limitations:

• This approach can add new items only. It cannot delete or correct items in the list.
• Be careful if the combo's RowSource contains criteria. The table may already have the NewData, in which case the attempt to append it will fail.

Option 2: Pop up a form

If the combo's source table has several fields, you need a form. Access 2007 gave combos a new property to make this very easy.

Using the List Items Edit Form property (Access 2007 and later)

Just set this property to the name of the form that should be used to manage the items in the combo's list. This approach is very simple, and requires no code.

The example below is for a CustomerID combo on an order form. When filling out an order, you can right-click the combo to add a new customer.

The combo properties (design view)	The right-click shortcut menu (to add a new customer)

Limitations:

• Previous versions of Access cannot do this.
• The form is opened modally (dialog mode), so you cannot browse elsewhere to decide what to add.
• The form does not open to the record you have in the combo. You have to move to a new record, or find the one you want to edit. (You can set the form's Data Entry property to Yes, but this does not make it easy for a user to figure out how to edit or delete an item.)

Using another event to open a form

To avoid these limitations, you could choose another event to pop up the form to edit the list. Perhaps the combo's DblClick event, a custom shortcut menu, or the click of a command button beside the combo. This approach does require some programming. There are several issues to solve here, since the edit form may already be open. 

Add code like this to the combo's event:

Private Sub CustomerID_DblClick(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim strWhere As String
    Const strcTargetForm = "Customers"
    
    'Set up to search for the current customer.
    If Not IsNull(Me.CustomerID) Then
        strWhere = "CustomerID = """ & Me.CustomerID & """"
    End If
    
    'Open the editing form.
    If Not CurrentProject.AllForms(strcTargetForm).IsLoaded Then
        DoCmd.OpenForm strcTargetForm
    End If
    With Forms(strcTargetForm)
    
        'Save any edits in progress, and make it the active form.
        If .Dirty Then .Dirty = False
        .SetFocus
        If strWhere <> vbNullString Then
            'Find the record matching the combo.
            Set rs = .RecordsetClone
            rs.FindFirst strWhere
            If Not rs.NoMatch Then
                .Bookmark = rs.Bookmark
            End If
        Else
            'Combo was blank, so go to new record.
            RunCommand acCmdRecordsGoToNew
        End If
    End With
    Set rs = Nothing
End Sub

Then, in the pop up form's module, requery the combo:

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler
    'Purpose:   Requery the combo that may have called this in its DblClick
    Dim cbo As ComboBox
    Dim iErrCount As Integer
    Const strcCallingForm = "Orders"
    
    If CurrentProject.AllForms(strcCallingForm).IsLoaded Then
        Set cbo = Forms(strcCallingForm)!CustomerID
        cbo.Requery
    End If
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    'Undo the combo if it has a partially entered value.
    If (Err.Number = 2118) And (iErrCount < 3) And Not (cbo Is Nothing) Then
        cbo.Undo
        Resume
    End If
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    If Response = acDeleteOK Then
        Call Form_AfterUpdate
    End If
End Sub

Option 3: Combos for entering free-form text

Set the combo's Limit To List property to No, and it lets you enter values values that are not in the list.

This approach is suitable for free-form text fields where the value might be similar to other records, but could also be quite different. For example, a comments field where comments might be similar to another record, but could be completely different.

The auto-expand makes it quick to enter similar comments, but it gives no accuracy. It is unnormalized, and completely unsuitable if you might need to count or group by the lookup category. If the combo's bound column is not the display column, you cannot set Limit To List to No.

To populate the combo's list, include DISTINCT in its Row Source, like this:
    SELECT DISTINCT Comments FROM Table1 WHERE (Comments Is Not Null) ORDER BY Comments;

Anything you type in the combo is saved in the Comments field. The list automatically shows the current items next time you open the form.

This approach is useful in only very limited scenarios.

Option 4: Add items to a Value List

In a word, DON'T! No serious developer should let users add items to value lists, despite Access 2007 introducing a raft of new properties for this purpose.

If you set a combo's Row Source Type to Value List, you can enter the list of items (separated by semicolons) in the Row Source. You might do this for a very limited range of choices that will never change (e.g. "Male"; "Female.") But letting the user add items to the list almost guarantees you will end up with bad data. 

Managing the Value List in the Form

Access 2007 introduced the Allow Value List Edits property. If you set this to Yes, you can right-click the combo and choose Edit List Items in the shortcut menu. Access opens a dialog where you can add items, remove items, or edit the items in the list.

Let's ignore the fact that this doesn't work at all if the combo's Column Count is 2 or more. The real problem is that there is no relational integrity:

• You can remove items that are actually being used in other records.
• You can correct a misspelled item, but the records that already have the misspelled item are not corrected.
• You can add items to your form, but in a split database, other users don't get these items. Consequently, other users add items with other names to their forms, even where they should be the same item.

If that's not bad enough, it gets worse when you close the form. Access asks:

Do you want to save the changes to the design of the form?

Regardless of how you answer that question, things go bad:

• If you answer No after using one of the new items, you now have items in the data that don't match the list.
• If you answer Yes in an unsplit database, you introduce strange errors as multiple users attempt to modify objects that could be in use by other people.
• If you answer Yes in an split database, the list of items in one front end no longer matches the lists in the others.
• Your changes don't last anyway: they are lost when the front end is updated.

There is no safe, reliable way for users to add items to the Value List in the form without messing up the integrity of the data.

Managing the Value List in the Table

What about storing the value list in the table instead of the form? Access 2007 and later can do that, but again it's unusable. Don't do it!

Some developers hate the idea of a combo in a table anyway. Particularly if the Bound Column is not the display value, it confuses people by masking what is really stored there, not to mention the issues with the wizard that creates this. For details, see The Evils of Lookup Fields in Tables. But lets ignore this wisdom, and explore what happens of you store the value list in the table.

Select the field in table design, and in the lower pane (on the Lookup tab), set the properties like this:

Now create a form using this table, with a combo for this field. Set the combo's Inherit Value List property to Yes. Now Access ignores the Row Source list in the form, and uses the list from the table instead. If you edit the list (adding, deleting, or modifying items), Access stores the changes in the properties of the field in the table.

Does this solve the problems associated with keeping the list in the form? No, it does not.

If the database is split (so the table is attached), the changed Value List is updated in the linked table in the front end only. It is not written to the real table in the back end. Consequently, the changed Value List is not propagated to other users. We still have the same problem where each user is adding their own separate items to the list. And we have the same problem where the user's changes are lost when the front end is updated.

(Just for good measure, the Row Source of the field in the linked table does not display correctly after it has been updated in this way, though the property is set if you examine it programmatically.)

At this point, it seems pointless to continue testing. One can also imagine multi-user issues with people overwriting each others' entries as they edit the data if the database is not split.

There is no safe, reliable way for users to add items to the Value List without messing up the integrity of the data.

Managing the Value List for Multi-Valued fields

Multi-valued fields (MVFs - introduced in Access 2007), suffer from the same issues if you let users edit their value list.

The MVFs have one more property that messes things up even further: Show Only Row Source Values. If you set this property to Yes, and allow users to modify the value list, it suppresses the display of items that are no longer in the list. A user can now remove an item from the list — even though 500 records in your database are using it. You will no longer see the value in any of the records where it is stored. At this point, not only have you messed up the integrity of the data, you have also messed up the display of the data, so no end user has any idea what is really stored in the database. (It can only be determined programmatically.)

Conclusion

Use tables, not value lists, to manage lookup data. Create relationships with Relational Integrity.

Use these lookup tables (or queries based on them), as the RowSource for your combos. Do not use Value Lists for anything more than the simplest of choices that the user never needs to edit.

Use the Not In List event to add data to simple, single-field lookups such as types or categories.

If you only use Access 2007 or later, the List Items Edit Form property is a quick and easy way to nominate the form to use for managing the list items.

To edit the list in any version of Access, or to control how the editing works, use another event such as the combo's DblClick.