Updating Combobox when Not in List is Active

Introduction.

This discussion focuses on the Limit to List property of the Combo Box. When this property is set to Yes, users cannot enter new values directly into the combo box; they are restricted to selecting from the existing list.

If a user attempts to type a value not already in the list, Access will display an error message, requiring them to choose from the available options.

For lists that never change—such as months of the year or days of the week—this behavior is perfectly acceptable. However, in scenarios where the list needs to evolve, such as adding new employees, products, or client names, the default approach becomes inconvenient.

By default, new items can only be added to the source table through a separate form. Even then, the updated value will not appear in the combo box until the form is closed and reopened, which is both time-consuming and not very user-friendly.

An Easy Solution.

However, we can make this process much easier for the user with a small VBA program. Instead of forcing them to open a separate form, the user can simply type the new value directly into the combo box. With their permission, the program will add the new entry to the source table and refresh the combo box instantly.

This is possible because when a user types a value that does not exist in the list, the Limit to List property triggers the combo box’s On Not In List event. By writing an Event Procedure for this event, we can prompt the user for confirmation, insert the new record into the source table, and then requery the combo box so the new item appears immediately.

The VBA Program.

The following program adds ProductID and Product Name into the Products Table and refreshes the cboProd Combo box to make the new item appear in the list immediately:

Private Sub cboProd_NotInList(NewData As String, Response As Integer)
Dim strProd As String, strName as String
Dim rst As Recordset, db As Database
Dim msg as string

On Error Goto cboProd_NotInList_Err

'continue without displaying error message
Response = acDataErrContinue

strProd = NewData
msg = "Product ID: " & strProd & " Not found in List!" & vbCr & vbCr & "Add it in Source File...?"

If MsgBox(msg, vbDefaultButton2 + vbYesNo + vbQuestion, "cboProd_NotinList()") = vbYes Then
    'Get Product Name from User
    strName=""
    'Repeat the loop if user presses enter key without entering a value
    Do While strName=""
        strName = InputBox("Product Name: ","cboProd_NotinList()","")
    Loop
    'Add ProductID and Name to the source Table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Products", dbOpenDynaset)
    With rst

      .AddNew
      ![ProductID] = strProd
      ![PName] = strName
      .Update
      .Close
    End With
    
    'make combobox control source empty
    'in preparation to refresh the combobox
    Me!cboProd = Null
    'refresh the combobox
    Me.cboProd.Requery
    'now the combobox list have the new entry
    'place the new code in the combobox control source
    Me![cboProd] = strProd
 
    Set rst = Nothing
    Set db = Nothing
Else
   'if user refuse to add the new entry into source file
   'then display the error message and exit
   Response = acDataErrDisplay
End If

cboProd_NotInList_Exit:
Exit Sub

cboProd_NotInList_Err:
MsgBox Err & " : " & Err.Description,,"cboProd_NotInList()"
Resume cboProd_NotInList_Exit

End Sub

The above code is run from the On Not in List Event Procedure of the cboProd Combo box.

• Stretching Controls when Resizing Form View
• Defining Pages on Form
• Accounting Year/Week Calculation
• Deleting Folders with DOS Command
• CHDIR and IN Clause of Access Query