Microsoft Access Topics: New Item in a Combo Box

Microsoft Access Topics:

New Item in a Combo Box

Introduction

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

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

Field Name Data Type Other Properties

FlavorID AutoNumber Primary Key

Flavor Text
Save it as Flavors and add a few items to the Flavor column such as Vanilla and Chocolate Cream

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

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

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

Close Microsoft Visual Basic and return to the form
Save the form and switch it to Form View
Process an order and click the Next Order button
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
Read the message and click Yes