Code It | Explore It

Download the sample database

This article demonstrates how to create combo boxes that accept new entries by using the NotInList event and the OpenArgs property of forms. To implement the solution, you use the NotInList event of a combo box to trap the error that occurs when a user types a value that is not in the underlying list. Then, you write an event procedure attached to that event. The event procedure opens a pop-up form to gather information about the new entry, adds the new entry to the list, and then continues where the user started.

To begin, load the sample database AddItemsToComboBox.accdb and open the frmDataEntry form in form view. Use the form to select a U.S. state from the combo box. For the sake of this example, the list of states in the combo box is incomplete. To add a new state, type its abbreviation in the form and answer Yes when asked whether you want to add a new record. Type the state name in the pop-up form that appears, and then click Done to return to the original data entry form, where your newly added state is already selected in the combo box.

Figure 1. Adding a new record by using the sample form

The sample form, frmDataEntry, uses some VBA code attached to the NotInList event of the combo box to pass information from the main form to the pop-up form, and then refresh the main form once the user closes the pop-up form.

The technique that is built in to Access 2007 simplifies the process, but gives up some flexibility. In the sample database, open the frmDataEntryAutomatic form, and add a new state. When you try to type a value that is not in the list, Access displays the following alert.

Figure 2. Adding a new item by using the technique that is built in to Access 2007

When you click Yes, Access displays the same bound form as before, (frmState), but does not fill in any of the data for you; you must know that you are supposed to type the same state information that you already typed on the form, along with the state name. When you close the pop-up form, Access adds the item to the underlying table for you, and refreshes the combo box on the main form.

Access can do all of this for you if you set the LimitToList property of the ComboBox to Yes, and set the ListItemsEditForm property to the name of a bound form that Access should display if you attempt to type a value that is not in the list (see Figure 3). Note however, that this technique works only in Access 2007, and does not necessarily make the process easy for the user, even if it is easier for the developer.

Figure 3. Access does the work for you if you specify these property values

Although it takes a bit more effort to handle the NotInList event manually, doing so gives you far more control over the behavior of the forms in your application.

To add the control to your own combo boxes

1. Make sure that your application has a table that contains lookup values. For example, a table similar to tblState in the sample database.

2. Import the basNotInList module from AddItemsToComboBox.accdb into your application.

3. Open your existing form in design view and create the combo box that you want to use to add and display records. Use the values in the following table to set the combo box properties.

   Table 1. Combo Box Property Settings

   Property	Setting
   RowSourceType	Table/Query
   RowSource	<Any table or query>
   LimitToList	Yes

4. Create an event procedure attached to the NotInList event of the combo box control. Add the following code to the event procedure (shown here for a control named cboState).

   VB

   Copy

   Private Sub cboState_NotInList( _
    newData As String, Response As Integer)
    
       Response = AddToCombo( _
        "frmState", "txtAbbreviation", newData)
   End Sub
   

5. Replace the arguments in the call to AddToCombo with the appropriate arguments for your own database. That is, specify the name of the form that you will use to add new records to the combo box, and the name of the control on the data entry form that matches the first displayed column of the combo box.

6. Create the pop-up form (frmState, in the sample) that you will use to add new values to the combo box. Use the values in the following table to set the form properties.

   Table 2. Form Property Settings

   Property	Setting
   RecordSource	The same table or query as the combo box's row source
   DefaultEditing	Data Entry
   OnLoad	[Event Procedure]

7. Add an event handler for the Load event of the form by adding the following code.

   VB

   Copy

   Private Sub Form_Load()
       CheckOpenArgs Me
   End Sub
   

8. Add controls to the pop-up form for all table fields that you need the user to complete. One of them should be the field that corresponds to the first visible column of the combo box; you supplied a name for this field in Step 4.

9. Save the pop-up form using the name that you supplied in Step 4.

Now open the main form that contains the combo box. Type a new value in the combo box. You should be prompted with a message box that asks you if you want to add a record. If you click Yes, the pop-up form opens to display the information that you typed in the combo box control. Type any other required information and then close the pop-up form to add the new information to the combo box list and view the new value already selected in the combo box.

When you set the LimitToList property to Yes in a combo box, Access generates a NotInList event when a user types in a value that is not in the list. In addition, it displays an error message by default. If you create a NotInList event procedure, however, you can intercept the error message before it occurs and add the record to the list yourself.

After you process the event, set the Response argument that Access provides to one of the following constants.

Table 3. Return Value Constants for the Response Argument

The solution in this article uses a general-purpose function in basNotInList, AddToCombo, to add a new record. To allow for the possibility that the user does not want to type a new value, the function displays a simple message box and quits if the user selects No. In that case, use acDataErrContinue as the return value so that Access suppresses the default error message, but does not try to add the new value to the combo box.

The following is the code for the AddToCombo function.

Public Function AddToCombo( _
 addFormName As String, controlName As String, _
 newData As String) As Integer

    ' Add a new record to a table by calling a form, and then
    ' requery the calling form. Designed to be called from
    ' NotInList event procedures
    '
    '   addFormName - the form to be opened to add a record
    '   controlName - the control on the add form that matches
    '       the displayed info in the calling combo box
    '   newData - the data as supplied by the calling combo box

    On Error GoTo HandleErr

    ' First, confirm the user really wants to type a new record.
    If MsgBox("Add new value to List?", vbQuestion + vbYesNo, _
     "Warning") = vbNo Then
        AddToCombo = acDataErrContinue
        Exit Function
    End If

    ' Open up the data add form in dialog mode, feeding it
    ' the name of the control and data to use
    DoCmd.OpenForm FormName:=addFormName, _
     DataMode:=acAdd, _
     WindowMode:=acDialog, _
     OpenArgs:=controlName & ";" & newData

    ' Before control returns to the calling form,
    ' tell it we've added the value
    AddToCombo = acDataErrAdded

ExitHere:
    Exit Function

HandleErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , _
     "AddToCombo"
    Resume ExitHere
End Function

If the user wants to add the new record, the function opens the pop-up form in dialog mode. That action pauses the function because a form open in dialog mode does not give up the focus until it is closed or hidden. The paused funtion, in turn, gives the user the opportunity to type the required data to complete the record.

' Open up the data add form in dialog mode, feeding it
' the name of the control and data to use.
DoCmd.OpenForm FormName:=addFormName, _
 DataMode:=acAdd, _
 WindowMode:=acDialog, _
 OpenArgs:=controlName & ";" & newData

Another issue that arises here is that you cannot fill in controls on the form before it is opened, and you cannot fill them in after it is opened because the form is open in dialog mode. The AddToCombo function gets around the issue by using the OpenArgs property of the pop-up form, which allows you to pass a text string to the form. (More on that a bit later though, when you look how the form uses the OpenArgs property to fill in its key field.)

After you close the pop-up form, the code must set the appropriate return value. In this case, returning acDataErrAdded tells Access that you added the value to the underlying table and now that value can be used as the value for the combo box.

' Before control returns to the calling form,
' tell it we've added the value.
AddToCombo = acDataErrAdded

If you look carefully at the code that the main form calls when it first opens the pop-up form, you'll see the following call to the DoCmd.OpenForm method.

DoCmd.OpenForm FormName:=addFormName, _
 DataMode:=acAdd, _
 WindowMode:=acDialog, _
 OpenArgs:=controlName & ";" & newData

This code concatenates the control name and its new data into a semi-colon-delimited pair. The pop-up form must retrieve the value from its OpenArgs property, parse it into two pieces, and place the new data into the correct location on the pop-up form.

When the pop-up form opens, the Load event calls the CheckOpenArgs function, which takes a form variable from the active form as its only parameter. This function processes the OpenArgs property of the form, which is where the form places the parameter that was passed to it when it was opened. The CheckOpenArgs procedure uses the following code.

Public Function CheckOpenArgs(frm As Form)

    ' Designed to be called on loading a new form
    ' Checks the OpenArgs and if it finds a string of
    ' the form
    '   controlname;value
    ' loads that value into that control

    Dim controlName As String
    Dim controlValue As String
    Dim semiColonPosition As Integer

    On Error GoTo HandleErr

    If IsNull(frm.OpenArgs) Then
        Exit Function
    Else
        semiColonPosition = InStr(frm.OpenArgs, ";")
        
        ' Was there a semi-colon?
        If semiColonPosition > 0 Then
            controlName = Left(frm.OpenArgs, semiColonPosition - 1)
            controlValue = Mid(frm.OpenArgs, semiColonPosition + 1)
            ' Possibly this OpenArgs belongs to someone else
            ' and just looks like ours. Set the error handling
            ' to just ignore any errors on the next line.
            On Error Resume Next
            frm.Form(controlName) = controlValue
        End If
    End If
    
ExitHere:
    Exit Function
    
HandleErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , _
        "CheckOpenArgs()"
    Resume ExitHere
End Function

The CheckOpenArgs function must avoid errors because it is called each time the form is opened. First, it is possible that no OpenArgs argument was passed in (perhaps you opened the form on its own). Second, the OpenArgs argument might be there for another reason--perhaps a different form opened the pop-up form and passed its own OpenArgs value to the form. Thus, if OpenArgs does not parse out as expected (in the format “ControlName;Value”), the procedure ignores it.

If OpenArgs is in the correct format, the code parses out the value to place in the corresponding control on the form.

The solution in this article is designed to be reusable, but you might find that you need a more specific function for a particular combo box. For example, you might add a Cancel button to the pop-up form for those users who decide against adding a new record. Or, you might want to add context to the data entry by using unbound text boxes on the data entry form to display pertinent information from the main form.

• Access 2007 Sample: Creating Combo Boxes that Accept New Entries

• Access Developer Center

• Access 2007 Resource Center

• Getting the Most Out of Visual Basic for Applications

• ComboBox.NotInListEvent

• ComboBox.ListItemsEditForm

• How-To: Add a Value to a Bound Combo Box