How do I add a value to a combo box with Not In List?

The combo box control has a nifty event called NotInList.  If the LimitToList property of my combo box is set to Yes, and I type a value that is not in the drop-down list, the NotInList event will fire.  This will allow me to capture this event and add some programming to it. 

Before I go further, I should explain that a combo box can have its drop-down list (the RowSource property) filled by a query, a field list or a value list.  The scenario I'm addressing here is a the row source filled by a query from a separate look-up table.

So suppose I have a form where I want to fill in Employee information including what Training they've had. There's a combo box on the form called cboTraining that is filled from a table called tbxTraining that lists the available training opportunities (Word, Excel, etc.).  Now suppose further that an employee takes a new type of training that is not already in the training table, say Access.  Since I have LimitToList set to Yes, I can't just type "Access" into the training field.  I have to select an item in the list.  But I also don't want my data entry person to have to stop what they're doing, open the tbxTraining table, add the value, then go back to entering data.

This is where the NotInList event comes in.  I can programmatically add the value to my tbxTraining table, refresh the combo box, and the data entry person can just continue on.

To add code to the combo's NotInList event, I open the form in design view and open the the property sheet of the combo box.  In the NotInList event, choose [Event Procedure] in the drop-down list.  The VBA editor will open with the first and last line of the subroutine already created.  Something like this:

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

End Sub

What I'm going to discuss now is the code that goes between those two lines.

There are a number of different ways to implement adding a value to the drop-down list.  Some are easier but dangerous.  Others are harder to implement, but safer.

The easiest thing for me to do is to just automatically add a new value to my lookup table when a new type of training is added to the transaction table. To do this, I would add something like the following to my NotInList event of my combo box:

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)
Dim strsql As String
MsgBox "Training type does not exist. Creating new Record in Training table."
strsql = "Insert Into [tbxTraining] ([Training]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
End Sub

You do want to be a little careful about allowing this depending on the data entry people. It's possible to get multiples of training with slight variations. For instance, a data entry person could add "Access" or "Access Database" to the training table, both of which would refer to the same type of training. I'm not saying this shouldn't be implemented, but you do need to be careful how you do it.

A little safer is to ask the user if they want to add it. That requires only a slight variation:

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into [tbxTraining] ([Training]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

This can still give me logical duplicates if my user isn't careful. (Sometimes they'll just say "yes" without ever thinking about it, which is just as bad as the first option).

Another way that is even safer is to programmatically open the maintenance form (say, frmTraining) when the user types a value that isn't in the list, allow them to add the record manually, then save and return to the record in the transaction form.

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
DoCmd.OpenForm "frmTraining"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

There are other variations on this theme.  On my website, I have a small sample database called NotInList.mdb which illustrates these and other options.

.