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.
804 928 4111