Dealing With a Combo Box Entry that is "Not In List"

Following on from my last post on Customizing an Access Combo Box, this tip is about dealing with a Combo Box Entry that is Not In List.  Basically this occurs when instead of selecting an item from the Combo Box list, the user manually types in an entry that is not one of the items from the drop down list. It works by running a small section of VBA code when the Combo Box NOT IN LIST Event fires (more about this later).  The code then creates a new record containing the user's entry in the table upon which the combo box list is based.

Let's use the example of a Combo Box on a Products Form to illustrate this.  The Combo Box is used in this example to enter the Category field of the Product record.  The Record Source of the form is tblProducts, and the Row Source of the Combo Box is tblCategory.  If you want to run a copy of this example, you can download the Not In List Example Database by clicking the link (you will need to Enable the Content if you save it a location that is not trusted).

Figure 1: The Products form with a Combo Box on the Category field.

As you can see from Figure 1 above, the Category field uses a Combo Box with a list of potential categories.  These are stored in a separate table called tblCategory, which is the value of the Combo Box ROW SOURCE property.  The screen shot shows that there are three categories - Office Equipment, Office Furniture, and Stationery.  So what happens if the user wants to create a new product record, for say, a software package? There is no existing category for Software, so once the user enters the product name - lets say it is MS Office Access 2010 - he or she is unable to select a suitable category from the drop down list.

Now might be good time to mention the Combo Box LIMIT TO LIST property (located in the DATA TAB of the PROPERTY SHEET).  When this is set to YES (which is the case in our example), Access would normally display an error message saying THE TEXT YOU ENTERED IS NOT AN ITEM IN THE LIST; it then asks the user to select an item which is, or type in text that matches one of the listed items. This message is show in Figure 2 below:

Figure 2: The default message shown when an item is not in list.

However, when LIMIT TO LIST is set to yes, Access first fires the NOT IN LIST event. This enables us to pre-empt the standard message by writing code to display a custom message of our own.  This code also gives the user the opportunity to add the new Category to the table which is the row source for the combo box list.

Let's take a look at the code used in our example database.

---

Private Sub ctlCategory_NotInList(NewData As String, Response As Integer)
On Error GoTo myError
    
    Dim rst As DAO.Recordset
        
    Set rst = CurrentDb.OpenRecordset("tblCategory", dbOpenDynaset)
    
        If vbYes = MsgBox("This Entry is not in list. Do you wish to add " _
                & NewData & " as a new category?", _
                vbYesNoCancel + vbDefaultButton2, _
                "New Category") Then
                
            rst.AddNew
                rst!categoryName = NewData
            rst.Update
            
            Response = acDataErrAdded
            
        Else
        
            Response = acDataErrContinue
            
        End If
       
leave:

    If Not rst Is Nothing Then
        rst.Close: Set rst = Nothing
    End If
    
    Exit Sub
    
myError:
   
    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave
    
End Sub

---

We enter this code into the Visual Basic Editor by clicking the three dot symbol on the far right of  ON NOT IN LIST (located on the EVENTS Tab of the Combo Box's PROPERTY SHEET).  The CODE BUILDER option was then selected from the CHOOSE BUILDER Dialogue Box.

Figure 3: The EVENTS Tab of the PROPERTY SHEET.

So how does the code work?

The first line of code was created automatically by Access when the VBA editor was opened via the ON NOT IN LIST line of the property sheet.  There are two arguments enclosed within the brackets - NewData and Response.  The first of these contains the new category value just entered by the user as a string variable.  The later relates to how access is going to handle the Not In List Event.  It's default value is 0 which represents the standard way of doing so ie displaying the error message and preventing the user from adding the new data.  Needless to say, we are going to alter this value later on in the code!

The third and fourth line of code relates to the object variable rst which represents a DAO recordset based on tblCategory, the Row Source of our Combo Box list.  Object variable rst is first declared in the Dim Statement, and then Set to tblCategory via the openrecordset method of the database object.

The fifth line of code relates to the custom message displayed to the user when he or she enters an item that is not in list.  The msgbox function displays our message as well as determining which buttons are offered, which button is the default, and the msgbox title.  The user response, ie the button the user clicks, is then processed by the If statement - so if the user clicks YES, the code branches to the code below where a new category record will be added.

This new category record is added using the addNew method of the Recordset Object.  The categoryName field is then set to the value of the NewData variable- ie the value of the new category entered by the user which was passed by Access as a parameter to the sub.  The new record is then saved to tblCategory using the Update method of the Recordset Object.

The next line of code changes the value held in the response variable (passed by Access as a parameter in the first line of the sub) via the acDataErrAdded constant.  Doing so tells access that new data can be added to the Combo Box list, and not to display the default Not In List error message.  It also Requeries the Combo Box list so that the new data appears immediately.

However, if the user had clicked the NO or CANCEL Command button mentioned above, the program flow branches to the Else Statement where it goes on to run the line of code changing the value held in the Response variable via the acDataErrContinue constant.  This tells Access not to add the new data to the Combo Box list, but continue without displaying the default Not In List message.

After the End If statement the program flow converges once again.  The last section of code (before the error handling section) deals with closing the rst Recordset Object and re-setting its value to nothing.  The sub's program flow then exits the sub via the Exit Sub statement.