New Item in a Combo Box

This article shows how to add a new item in a combo box that the user is trying to select

1. Create a database called IceCream1
2. Create a first table with the following fields:
    

   Field Name	Data Type	Other Properties
   FlavorID	AutoNumber	Primary Key
   Flavor	Text

3. Save it as Flavors and add a few items to the Flavor column such as Vanilla and Chocolate Cream
4. Create a second table with the following fields:
    

   Field Name	Data Type	Other Properties
   OrderID	AutoNumber	Primary Key
   DateOrdered	Date/Time	Format: dd-mmm-yyyy Input Mask: 99\->L<LL\-0000;0;_
   FlavorID	Lookup Wizard

5. When the Lookup Wizard starts, select the Flavor field of the Flavors table and accept all defaults
6. Save the table as Orders
7. Create a form for the Orders table and save it as Orders
8. Display the form in Design View and double-click the FlavorID combo box
9. In the Events tab of the Properties window, double-click On Not In List and click its ellipsis button
10. Implement the event as follows:
     

    Private Sub FlavorID_NotInList(NewData As String, Response As Integer) On Error GoTo SomethingBadHappened Dim rstFlavors As ADODB.Recordset Dim intAnswer As Integer intAnswer = MsgBox("Add " & NewData & " to the list of flavors?", _ vbQuestion + vbYesNo) If intAnswer = vbYes Then Set rstFlavors = New ADODB.Recordset rstFlavors.Open "Flavors", CurrentProject.Connection, _ adOpenStatic, adLockOptimistic, adCmdTable rstFlavors.AddNew rstFlavors!Flavor = NewData rstFlavors.Update Response = acDataErrAdded Else Response = acDataErrDisplay End If rstFlavors.Close Set rstFlavors = Nothing Exit Sub SomethingBadHappened: MsgBox "When trying to process this order, something bad happened" & _ vbCrLf & "Please contact the program vendor and " & _ "report the error as follows" & vbCrLf & _ "Error #: " & Err.Number & vbCrLf & _ "Description: " & Err.Description Resume Next End Sub

11. Close Microsoft Visual Basic and return to the form
12. Save the form and switch it to Form View
13. Process an order and click the Next Order button
14. After adding a date, enter a non-existing flavor such Butter Pecan in the FlavorID combo box and click the Date Ordered text box again
15. Read the message and click Yes