NotInList Adding a Value to a Combo Box at Runtime

The NotInList event property is triggered when you enter a value into a combo box that is not contained within the list. This event property and procedure works in conjunction with the LimitToList property, which is a Boolean property of a combo box that instructs Access to restrict selections to values in the list.

This example comes from the need to add a new value to the underlying table used to populate a combo box list. Because a combo box was designed to display values from a table, it means that the value must already be in the table in order to be displayed.

In some combo boxes, it may be important to restrict the data to a finite list; for example, hotel room numbers. You would not want to allow a customer to be assigned to a room number that doesn't physically exist. If the hotel were to build another wing, then the new room numbers would be added by the administrator, for the use of the desk clerk.

On the other hand, there may be situations where it is not feasible or cost-effective to stop data entry and request that the Admin enter a new value. Instead, the combo box is used to increase data entry speed, but only to limit excessive similar entries from being added. For example, job titles are sometimes very common, but other times outrageously unique. Receptionist, Administrative Assistant, and Project Manager are all standard values that are repeatable values, but Senior Herbal Fragrance Arranger may be a less common choice.

You would not like to restrict the user from being able to enter the unique titles, but on the other hand, you would like to try to prevent a myriad of permutations of similar values from being entered. For example, Admin Asst, Administrative Asst, and Admin Assistant all convey the same information. So, given these constraints, you should not lock down the list, but you can at least try to prevent the endless variety of entries, by presenting the user with those common values, so that they can quickly choose one and move to the next field, without a second thought.

This is the premise of the example, and the following code provides a method to perform this type of action. There are two functions supplied in the excerpt: AddItem_Prompt and AddItem_ToTable. The prompting routine is only called once from the data addition procedure, but supposing that there are other procedures that called the prompt routine would be reason enough to separate it.

Private Function AddItem_Prompt( _

pstrItemType As String, pstrNewData As String) As Boolean 'Prompt the user to add a new value to the combo box 'strItemType - Type of Item to be added(Customer, Category, Product) 'varNewData - NewData from Combo Box Dim strMsg As String

'Create a custom prompt for the type of data to be collected strMsg = & pstrNewData & "' is not in the list." & _

vbNewLine & vbNewLine & _ "Would you like to add it?"

'Show the prompt, and get response

If vbYes = MsgBox(strMsg, vbYesNo + vbQuestion, _

"Unknown " & pstrItemType) Then

AddItem_Prompt = True

End If

End Function

Public Function AddItem_ToTable( _

pstrNewData As String, pstrTable As String, _

ParamArray pastrFields()) As Integer

'This routine will prompt the user to add data when an

'unknown value is entered into a Combo Box


' strItem = Unknown data entered by user

' strTable = Lookup Table

' strFields(0) = Prompt for Input Box

' strFields(1) = Field Name in table

'Call Example 1: Add 'FMS, Inc.' to Company Table

'AddItem("FMS, Inc.", "tblCompany", "Company Name", "CoName")

'Example 2: Add 'IS' & "Information Systems" to Department


'AddItem("IS", "tblDepartment", "Department Code", _

"DeptCode", "Department Name", "DeptName")

'Example 3: Add 'Clark' & User entered First Name and Middle Initial

to Customer Table

'AddItem("Clark", "tblCustomer", _

"Last Name", "LName", _

"First Name","Fname", _

"Middle Initial", "MI")

Dim strMsg As String

Dim i As Integer

Dim varVal As Variant

Dim rs As New ADODB.Recordset

'Begin by setting the default response to be to show the

'standard error message

AddItem_ToTable = acDataErrDisplay

'Prompt the user to add the new value

If AddItem_Prompt(CStr(pastrFields(0)), pstrNewData) Then

'Store the number of parameters passed in procedure call

intMax = UBound(pastrFields(), 1) +1

'Create a recordset based on the tablename provided

'in the procedure call

If OpenADORecordset(rs, pstrTable, _

adLockOptimistic, , adCmdTable) Then

With rs

'Start an new record


'Write the data to the specified field

.Fields(pastrFields(1)) = pstrNewData i = 2

'Repeat this process for each parameter passed

Do While i < intMax

'Prompt the user for any additional information

varVal = _

InputBox("Enter " & pastrFields(i), "Add Item to


'Store the additional information .Fields(pastrFields(i +1)) = varVal i = i + 2


'Save the new record .Update End With

AddItem_ToTable = acDataErrAdded End If End If End Function

The explanation of this example starts with the return values that are assigned to the AddItem_ToTable procedure. Although it is declared as an integer, it is really an intrinsic constant value that is native to Access.

An intrinsic constant is simply a variable that is supplied natively with Access and assigned a permanent value. It is something that is easier to remember than a number like 1, 64, or 256. The following table lists the possibilities for the value.

Intrinsic Constant Name



Suppresses the standard error message


Suppresses the standard error message, and refreshes the entries in the combo box


Displays the standard error message

Looking through the code, you will notice that only two of the intrinsic constants are used. The third, AcDataErrContinue, is not used. When the custom routine above finishes, either the error message will show, or the data will be added, and the data is refreshed. Now that you understand what these values mean, you need to know where the value is applied. To understand that, refer to the following code:

Private Sub cboTitle_NotInList(NewData As String, Response As Integer) Response = AddItem_ToTable(NewData, "tblTitle", _ "Title", "Title")

End Sub

The code above is the declaration of the NotInList event procedure for a combo box. It is important to examine the two parameters that are exposed to the developer. The two values are NewData and Response.


Remember that the object of this event procedure is to execute code when the value entered is not in the list. With this in mind, it makes sense that the NewData parameter is that unknown value. It is exposed to the developer for your convenience. Strangely enough, you can write to this parameter, but doing so will lose the unknown value.


On the other hand, the Response parameter, which was covered in the table above, is used to instruct Access what action to take when the procedure terminates. In the code, you can see that the Response parameter is set to the value returned by the AddItem_ToTable function. If you review the code to this function, you will see that initially, the function is set to acDataErrDisplay. At the end of the function, when the function is successful, meaning that the unknown value was added to the data table, the parameter is set to acDataErrAdded.


Continuing with the exploration of the AddItem_ToTable function, notice that there is a procedure parameter that starts with the reserved word ParamArray. The ParamArray is an Array (a data storage mechanism in memory) that can accept any number of parameters. The Array is always set to the Variant datatype, and it is unlimited as to the number of values that it can receive. So, much like the OpenArgs parameter of the form, this is a way to pass anything you could possibly think of or ever need.

In the case of this particular procedure, the ParamArray is used to assist in the capture of even more data than just the one value that was entered in the combo box. Implementing this gives the ability to enter a second, third, or nth piece of information pertaining to the new data value. For example, suppose a new department code is added, you code-prompt the user for the new department name as well. If you capture only the code, then it would require for someone to return to the Department Table (or a form that displays the table's data) to update the Department Name. So, it is a luxury to this process, but quite useful. You should refrain from prompting for too many values, as the user really does want to finish the work at hand, not answer prompts all afternoon. After two prompts, it may be a better idea to require the opening of an entire data entry form to capture the information.

Included in the code is a call to a custom procedure called OpenADORecordset. This is a function that takes care of setting a connection string, issuing the appropriate lock, and then populating the recordset that is passed to it. Your code may not compile due to this procedure, so feel free to either replace it or create your own to fulfill the need.

The rest of the procedure is spent stepping through each of the parameter values, prompting for needed values, entering the value into the table in the accompanying field name, and then returning either a success or failure value to the calling procedure.

0 0

Post a comment