Microsoft Access Database Development With VBA

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

  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
 
 
     
 

Home Copyright © 2012-2013 FunctionX, Inc. Home