NotInList Event -- Event Procedures

NotInList Event — Event Procedures

Description

To create an event procedure that runs when the NotInList event occurs, set the OnNotInList property to [Event Procedure], and click the Build button.

Syntax

Private Sub controlname_NotInList(NewData As String, Response As Integer)

The NotInList event procedure has the following arguments.

Argument	Description

controlname	The name of the control whose NotInList event procedure you want to run.
NewData	A string that Microsoft Access uses to pass the text the user entered in the text box portion of the combo box to the event procedure.
Response	The setting indicates how the NotInList event was handled. The Response argument can be one of the following intrinsic constants:
	Constant	Description

	acDataErrDisplay	(Default) Displays the default message to the user. You can use this when you don't want to allow the user to add a new value to the combo box list.
	acDataErrContinue	Doesn't display the default message to the user. You can use this when you want to display a custom message to the user. For example, the event procedure could display a custom dialog box asking if the user wanted to save the new entry. If the response is Yes, the event procedure would add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, the event procedure would set the Response argument to acDataErrContinue.
	acDataErrAdded	Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by requerying the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message.

Remarks

You can create an event procedure for the NotInList event that provides a way for the user to add a new item to the combo box list. For example, you can add a record to the table that supplies the list's values, or add a value to the value list that is the source for the combo box list.

To add a new entry to a combo box list, your event procedure must add the value in the NewData argument to the source of the combo box list. How you do this depends on the type of source the combo box list uses, as determined by the RowSourceType and RowSource properties of the combo box. In the Example in this topic, the event procedure adds the new value to a value list for the combo box.

If you let the user change the value originally typed in the combo box (for example, in a custom dialog box), you must set the combo box value to the new entry entered in the custom dialog box. This saves the new value in the field the combo box is bound to. Set the Response argument to acDataErrContinue, and Microsoft Access will add the new value to the combo box list.

See Also

NotInList event — macros.

Example

The following example uses the NotInList event to add an item to a combo box.

To try this example, create a combo box called Colors on a form. Set the combo box's LimitToList property to Yes. To populate the combo box, set the combo box's RowSourceType property to Value List, and supply a list of values separated by semicolons as the setting for the RowSource property. For example, you might supply the following values as the setting for this property: Red; Green; Blue.

Next add the following event procedure to the form. Switch to Form view and enter a new value in the text portion of the combo box.

Private Sub Colors_NotInList(NewData As String, _
        Response As Integer)
    Dim ctl As Control
    
    ' Return Control object that points to combo box.
    Set ctl = Me!Colors
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        ctl.RowSource = ctl.RowSource & ";" & NewData
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If
End Sub

Note The above example adds an item to an unbound combo box. When you add an item to a bound combo box, you add a value to a field in the underlying data source. In most cases you can't simply add one field in a new record — depending on the structure of data in the table, you probably will need to add one or more fields to fulfill data requirements. For instance, a new record must include values for any fields comprising the primary key. If you need to add items to a bound combo box dynamically, you must prompt the user to enter data for all required fields, save the new record, and then requery the combo box to display the new value.