Select Dropdowns in MS Access: Combo Boxes, List Boxes & Multi-Select Setup

Searchers asking about select dropdowns in MS Access often hit one confusion immediately: a standard combo box allows only one selection per field. True multi-select dropdown behavior requires a list box with Multi Select set to Simple or Extended, plus a junction table and VBA to persist multiple choices. This guide covers both patterns — single-select combo boxes populated from lookup table Access designs for everyday data entry, and multi-select list boxes for tagging projects with several departments or assigning multiple skills to an employee. You will set Row Source properties, wire Bound Column correctly, save selections with VBA, and fix the errors developers actually see in production forms.

Choose a combo box when the user picks one customer, one department, or one status from a list — ninety percent of Access form controls fit this pattern. Choose a multi-select list box when one record genuinely needs many related values stored in a separate junction table, such as tblProjectDepartments linking ProjectID to multiple DepartmentID rows.

• Combo box (single-select dropdown)Compact, supports Limit To List and type-ahead search. Stores one foreign key in the parent table — CustomerID, DepartmentID, StatusCode.
• List box (multi-select dropdown workaround)Shows several rows at once. Multi Select Extended lets users CTRL+click multiple items. Selections save to a junction table via VBA — not in a single text field.

Before adding Access form controls, structure lookup and junction tables correctly. A typical multi-select scenario uses three tables:

1. Create tblDepartments (lookup table)
   • DepartmentIDAutoNumber primary key (stored in junction table).
   • DepartmentNameText shown in the list box — Sales, IT, HR, Finance.
   Sample rows: (1, Sales), (2, IT), (3, HR), (4, Finance).
2. Prepare tblProjects (main table)ProjectID (AutoNumber), ProjectName, StartDate. Do not store comma-separated department names here — that breaks normalization and reporting.
3. Create tblProjectDepartments (junction table)ProjectDepartmentID (AutoNumber), ProjectID (FK), DepartmentID (FK). Each row is one project-to-department link. The list box VBA inserts and deletes rows here.

For single-select dropdowns, skip the junction table — add DepartmentID directly on tblProjects as a foreign key bound to a combo box. Review how to create forms in MS Access before wiring controls in Design View.

1. Open the form in Design ViewOpen frmProjects (or your target form) and switch to Design View. Confirm the form Record Source is tblProjects so RecordID and ProjectID are available to VBA save routines.
2. Add a list box — not a combo boxFrom the Controls gallery, add a List Box. Combo boxes ignore Multi Select; only list boxes support multiple selections. Size the control tall enough to show at least four department rows without scrolling.
3. Set Multi Select propertyOpen Property Sheet, select the list box, set Multi Select to Extended (CTRL+click) or Simple (click toggles selection). None is the default and behaves like single-select only — a common pitfall when copying controls from combo box templates.
4. Configure Row Source and columnsSet Row Source Type to Table/Query and Row Source to tblDepartments. Set Column Count to 2, Bound Column to 1, Column Widths to 0";1.5" — hide DepartmentID, display DepartmentName. Requery the list box in Form Load to refresh options after lookup table edits.
5. Load existing selections on openIn Form_Load or when the current record changes, loop tblProjectDepartments for the active ProjectID and set ListIndex.Selected = True for each matching DepartmentID. Without this step, saved selections appear blank when users reopen the record.
6. Save selections with VBA on button click or Before UpdateDelete existing junction rows for the current ProjectID, then loop ItemsSelected to insert new rows. Never store multi-select values in a single memo field — queries and reports cannot filter comma-separated text reliably.

Paste the procedure below into the form's code module (right-click form, Design View, then View Code). Attach SaveSelections to a Save Departments button On Click event, or call it from the form Before Update event after validating at least one department is selected. Replace lstDepartments with your list box name, tblProjectDepartments with your junction table, and confirm RecordID matches your primary key field name (ProjectID).

Private Sub SaveSelections()
    Dim ctl As Control
    Dim varItem As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set ctl = Me.lstDepartments
    Set db = CurrentDb
    
    ' Remove prior selections for this project
    db.Execute "DELETE FROM tblProjectDepartments WHERE ProjectID=" & Me.ProjectID, dbFailOnError
    
    Set rs = db.OpenRecordset("tblProjectDepartments", dbOpenDynaset)
    
    For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!ProjectID = Me.ProjectID
        rs!DepartmentID = ctl.ItemData(varItem)
        rs.Update
    Next varItem
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    MsgBox "Departments saved.", vbInformation
End Sub

After wiring combo boxes or multi-select list boxes, test with fresh records and existing records that already have junction rows. Common errors and fixes:

• "Item Not Found in List" or invalid field valueBound value is missing from Row Source — lookup row was deleted or Bound Column does not match stored ID. Set Limit To List to Yes on combo boxes to catch typos at entry time.
• List box shows IDs instead of namesColumn Widths hides the wrong column — use 0";2" to hide the ID column and show text in column 2. Confirm Column Count matches your Row Source field count.
• Multi-select saves only the last clicked itemMulti Select is still None, or code reads ListIndex instead of looping ItemsSelected. Verify Property Sheet shows Extended or Simple before debugging VBA.
• Empty junction table after saveItemData returns Null when Bound Column is wrong, or DELETE ran but AddNew failed silently. Wrap db.Execute and rs.Update in error handling and check Me.ProjectID is not Null on new records.
• Dropdown options stale after lookup editCall Me.lstDepartments.Requery or Me.cboDepartment.Requery in Form Load and after append queries that add lookup rows.

• Assign multiple departments to a project or grant without duplicate project rows.
• Tag products with several categories for filtered catalog reports.
• Grant role-based permissions by linking users to multiple security groups in a junction table — pair with guidance on permissions in MS Access for production deployments.

Single-select combo boxes remain the default for customer, vendor, and status fields on Microsoft Access database entry forms — use multi-select list boxes only where the data model truly requires many related values per record.

• Can MS Access have a multi-select dropdown?Not with a standard combo box — combo boxes allow only one selection per field. Multi-select requires a list box control with Multi Select set to Simple or Extended, plus a junction table and VBA to save multiple ItemData values per parent record.
• What's the difference between a combo box and a list box in MS Access?A combo box shows one value with a dropdown arrow and supports type-ahead search — ideal for long lookup lists on data entry forms. A list box displays multiple rows at once and supports Multi Select for choosing several options, but takes more form space.
• How do I populate a dropdown from a table in Access?Set the control Row Source Type to Table/Query and Row Source to tblDepartments or a saved query. Set Bound Column to the hidden ID field and Column Count to 2 with Column Widths like 0";2" so users see DepartmentName but the form stores DepartmentID.
• What does the Row Source property do on an Access form control?Row Source defines where dropdown options come from — a table name, query SQL, or Value List string. Row Source Type must match: Table/Query for lookup tables, Value List for fixed options like Active;Inactive, Field List for fields from the form record source.
• Why do I get "The value you entered isn't valid for this field" on my combo box?The bound value is not in the Row Source list — often because Bound Column stores DepartmentID but the user typed text not in the lookup, or Limit To List is Yes and the value was deleted from tblDepartments. Requery the combo after updating lookup tables.
• How do I save multiple list box selections to related records?Use a junction table such as tblProjectDepartments with ProjectID and DepartmentID. On form Save or button click, loop through ListBox.ItemsSelected in VBA, read ItemData for each index, and insert rows into the junction table — delete existing rows for that ProjectID first to avoid duplicates.

Select dropdowns in MS Access split into two clear patterns: combo box controls for single-select lookups with Row Source from tblDepartments or similar, and list box controls with Multi Select plus junction tables for true multi-select dropdown MS Access workflows. Configure Bound Column, Column Widths, and Requery events carefully, save list box selections with ItemsSelected VBA, and test for invalid-value errors before deploying to multi-user front-ends. For formatted output of tagged data, explore Microsoft Access reporting tools. Need help wiring complex lookup forms? Review securing Microsoft Access databases before rolling permission multi-select controls to production.