Mastering Combo Boxes in Microsoft Access Forms

The combo box is arguably the most useful control in Microsoft Access. It combines a text box and a dropdown list, letting users either type a value or select from a predefined list. When configured correctly, combo boxes enforce data consistency, speed up data entry, and make your forms feel polished and professional.

Combo Box Basics

A combo box has two key properties that control what it displays and what it stores:

Row Source — the data that populates the dropdown list. This can be:

• A table name: Customers
• A query name: qryActiveCustomers
• A SQL statement: SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName
• A value list: "Active";"Inactive";"Pending"

Bound Column — which column from the Row Source gets stored in the underlying field. Column numbering starts at 1.

Column Count — how many columns from the Row Source to include in the dropdown.

Column Widths — the width of each column in the dropdown. Set a column width to 0 to hide it.

The Classic Pattern: Display Name, Store ID

The most common combo box pattern displays a human-readable name but stores the underlying ID. For example, a combo box on an Orders form might show customer names in the dropdown but store CustomerID in the Orders table.

Setup:

• Row Source: SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName
• Column Count: 2
• Column Widths: 0cm;5cm (hide the ID column, show the name column)
• Bound Column: 1 (store CustomerID)

The user sees names in the dropdown, but the Orders table stores the CustomerID. This maintains referential integrity and keeps your data normalized.

Value Lists for Simple Dropdowns

For a small, fixed set of options, use a value list instead of a table:

• Row Source Type: Value List
• Row Source: "Active";"Inactive";"Pending";"Cancelled"

Value lists are quick to set up and do not require a lookup table. Use them for status fields, categories, and other controlled vocabularies with fewer than 10-15 options. For larger or more dynamic lists, use a table.

Cascading Combo Boxes

Cascading combo boxes are a powerful pattern where the selection in one combo box filters the options in another. For example, selecting a Country filters the State/Province combo box to show only states in that country.

Implementation using VBA:

1. Create the first combo box (e.g., cboCountry) with a Row Source of the Countries table
2. Create the second combo box (e.g., cboState) with an initial Row Source of the States table
3. In the After Update event of cboCountry, requery cboState with a filtered Row Source:

Private Sub cboCountry_AfterUpdate()
    ' Update the state combo box to show only states for the selected country
    Me.cboState.RowSource = "SELECT StateID, StateName FROM States " & _
                             "WHERE CountryID = " & Me.cboCountry.Value & _
                             " ORDER BY StateName"
    Me.cboState.Requery
    Me.cboState.Value = Null  ' Clear the current state selection
End Sub

4. Optionally, add a similar cascade from State to City if needed

The NotInList Event

By default, if a user types a value that is not in the combo box list, Access either accepts it (if Limit To List is No) or shows an error (if Limit To List is Yes). The NotInList event lets you handle this gracefully — for example, by offering to add the new value to the lookup table:

Private Sub cboCustomer_NotInList(NewData As String, Response As Integer)
    Dim answer As Integer
    answer = MsgBox("'" & NewData & "' is not in the customer list. Add it now?", _
                    vbYesNo + vbQuestion, "New Customer")
    
    If answer = vbYes Then
        ' Open the customer form to add the new customer
        DoCmd.OpenForm "CustomerForm", , , , acFormAdd
        Forms!CustomerForm!CustomerName = NewData
        Response = acDataErrAdded  ' Tell Access to requery the combo box
    Else
        Response = acDataErrContinue  ' Clear the invalid entry
        Me.cboCustomer.Undo
    End If
End Sub

Set Limit To List to Yes to trigger this event when an unrecognized value is entered.

Auto-Expanding and Auto-Completing

The Auto Expand property (default: Yes) automatically completes the combo box text as the user types. As the user types "Sm", Access jumps to the first matching entry ("Smith, Alice").

For large lists, Auto Expand can be slow. Set it to No if performance is an issue, or ensure the combo box's Row Source query is indexed on the display column.

Searching with a Combo Box

A common pattern is to use a combo box as a search control — the user selects a name from the dropdown, and the form navigates to that record:

Private Sub cboSearch_AfterUpdate()
    ' Find the record that matches the combo box
    Dim rs As Object
    Set rs = Me.RecordsetClone
    rs.FindFirst "[CustomerID] = " & Me.cboSearch.Value
    If Not rs.NoMatch Then
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
    Me.cboSearch.Value = Null  ' Clear the search box
End Sub

Place this combo box in the form header so it is always visible regardless of which record is displayed.

Performance Tips for Large Lists

Combo boxes with large row sources (thousands of records) can slow down form loading:

• Index the display column in the source table
• Limit the columns — use only the columns you need, not SELECT *
• Add a WHERE clause to filter the list (e.g., only active records)
• Set Auto Expand to No for very large lists
• Use a search form instead of a combo box for lists over 10,000 records

Conclusion

Combo boxes are worth mastering. The display-name/store-ID pattern alone will improve the quality of your data significantly by enforcing referential integrity while keeping forms user-friendly. Add cascading dropdowns and the NotInList event, and you have the building blocks for professional, polished data entry interfaces that users will actually enjoy using.