To create an event procedure that is executed when the NotInList event occurs, set the OnNotInList property to [Event Procedure], and click the Build button.
Private Sub controlname_NotInList(NewData As String, Reponse As Integer)
The NotInList event procedure uses the following arguments
|
Argument |
Description |
|
controlname |
A string that is the name of the control affected by the NotInList event procedure. |
|
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. |
|
Argument |
Description |
|
Response |
An intrinsic constant that indicates how the NotInList event was handled. The Response argument can have the following constants. |
|
Constant acDataErrDisplay |
Description (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 NewData in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message. |
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.
NotInList Event — Macros.
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 LimitToList property of the combo box to Yes. To populate the combo box, set the RowSourceType property of the combo box 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 code to the Declarations section of 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 IfSub
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.