Change a Microsoft Access Combo Box Search Field with VBA
Question details
Change an Access combo box to display device names or assigned user names according to an option group selection.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A form uses an option group to select which field supplies the combo box list.
- Observed behavior
- The combo box should refresh with the selected field and exclude null assigned user names when that option is selected.
Open the form in Design View and check the names of the option group, combo box, table, and fields. Confirm that the option group values are 1 for device names and 2 for assigned user names, or adjust the code to match your values.
Update the combo box RowSource in the option group's After Update event
Use this VBA event procedure to switch the list when the user changes the search option.
In the form's Design View, select the combo box and open Property Sheet. On the Data tab, set Row Source Type to Table/Query. On the Format and Data tabs, configure two columns and bind the ID column as needed.
Select the optSearchBy option group, open Property Sheet, and choose the Event tab. Set After Update to [Event Procedure], then click the build button to open the VBA editor.
In the optSearchBy_AfterUpdate procedure, use this code, replacing object names if yours differ: Private Sub optSearchBy_AfterUpdate() If Me.optSearchBy = 1 Then Me.cboSelect.RowSource = "SELECT ID, DeviceName FROM DeviceInfoTable ORDER BY DeviceName;" ElseIf Me.optSearchBy = 2 Then Me.cboSelect.RowSource = "SELECT ID, AssignedUserName FROM DeviceInfoTable WHERE AssignedUserName IS NOT NULL ORDER BY AssignedUserName;" Else MsgBox "Please select a search option." Exit Sub End If Me.cboSelect.Requery End Sub
Save the form, switch to Form View, and select each option in optSearchBy. Open cboSelect to confirm that the list changes and that records with null assigned user names are excluded.

Work with device lists in WPS Office
WPS Office is a free, lightweight alternative for working with device and user lists in spreadsheets. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files. It does not replace the Access VBA form or fix its combo box.

Frequently Asked Questions
Where should I put the VBA code?
In the form's Design View, select the option group and open Property Sheet > Event > After Update. Choose [Event Procedure] and click the build button to edit the procedure.
Why do I need to requery the combo box?
After assigning a new SQL statement to Me.cboSelect.RowSource, run Me.cboSelect.Requery so Access refreshes the combo box list using the new source.
How do I exclude null user names from the list?
Add WHERE AssignedUserName IS NOT NULL to the assigned-user SQL statement. Do not compare the field to the text "isNull".
Why does the combo box show an ID instead of the name?
In Design View, check the combo box's Column Count, Bound Column, and Column Widths properties. The query returns ID first and the display name second; you can hide the ID column while keeping it as the bound value.




