Populating a combo box with a new value

I really am not a fan of the code examples out there for adding an item to a drop down list.  I’d like to give you a simple example of how to approach.

So here’s the scenario:

You’ve got a drop down (cboItemID) of Items (tbl_Items) based on an item table with a key field of itemid.

You want to enter an item into the drop down list that’s not in the tbl_Items table. So you want to add the item to the item table, and then the drop down will accept the new item.

Here’s how I did it:

1 – Set drop down (combo box) LimitToList property to ‘Yes’

2 – Set the drop down On NotInList property to [event procedure] and enter the following code:

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

    Dim intReply As Integer

    intReply = MsgBox(NewData & " is not in the list of Items. Do you wish to add it?", vbYesNo, "Add Item?")

    If intReply = 6 Then
        CurrentDb.Execute "INSERT INTO tbl_Items ( Item_Description ) SELECT '" & NewData & "' AS Item_Description"
        Response = acDataErrAdded
    Else
        cboItemID = Null
        Response = acDataErrContinue
    End If
 
End Sub

Please note that I made the assumption that cboItemID is a 2 column combo box with ItemID as the first column and Item_Description as the second Column.

If you have any question on this code, please feel free to email me or give me a call. I’m happy to help out.

Jack Jaeger
Data Control
jjaeger@datacontrolllc.com
804 928 4111