How to Clear an Access Combo Box When Moving to Another Record
Question details
The user needs to reset, clear, or synchronize an unbound combo box on a Microsoft Access form when navigating to a different record.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Navigating between records in a Microsoft Access database form that contains an unbound search or navigation combo box.
- Observed behavior
- The unbound combo box retains its previous selection when moving to a new record, causing the displayed value to appear inconsistent with the current record's actual data.
Before modifying your form's code, open your form in Design View and take note of the exact name of the combo box control you wish to clear, as you will need to reference it in VBA.
Use the Form_Current Event to Clear or Synchronize the Combo Box
Add a clearing statement to the form's Current event so the combo box resets automatically every time you navigate to a different record.
The Form_Current event triggers whenever the focus moves to a record, making it the ideal place to reset unbound controls.
Ensure you add your code to the existing Form_Current procedure rather than creating a duplicate, which will cause a compile error.
Right-click your form in the Access navigation pane and select 'Design View'.
Open the Property Sheet, select 'Form' from the selection type dropdown, go to the 'Event' tab, find 'On Current', and click the ellipsis (...) to open the VBA editor.
Inside the Private Sub Form_Current() block, type 'Me.yourComboBoxName = Null' (replace 'yourComboBoxName' with the actual name of your control).
If you prefer the combo box to display the current record's value instead of being blank, type 'Me.yourComboBoxName = Me.primaryKeyControl' instead.
Use the AfterUpdate Event to Clear After Selection
Clear the combo box immediately after the user makes a selection and the associated action (like finding a record) is completed.
Looking for a Lightweight and Free Office Suite?
While Microsoft Access handles your complex database management, you might also need an efficient, cost-effective solution for your documents, spreadsheets, and presentations. WPS Office is a powerful, free alternative to Microsoft Office that is fully compatible with Word, Excel, and PowerPoint files.
- 1. Download the installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the suite: Run the setup file and follow the on-screen instructions to install WPS Office on your device.
- 3. Start creating and editing: Open WPS Office to instantly create, edit, and save documents in standard Microsoft Office formats.
Frequently Asked Questions
Why does my unbound combo box keep its value across records?
Unbound controls are not tied to a specific field in the underlying table or query. Because they are independent of the record source, their values persist until they are manually cleared or changed via VBA code, even when the form navigates to a new record.
What happens if I create two Form_Current procedures?
Microsoft Access VBA will throw an 'Ambiguous name detected: Form_Current' compile error. You cannot have two subroutines with the same name in the same form module. You must combine all code meant to run on record changes into a single Private Sub Form_Current() block.
Can I set the combo box to match the current record instead of clearing it?
Yes. Instead of setting it to Null, you can synchronize the unbound combo box by assigning it the value of the current record's primary key. You can achieve this by adding 'Me.comboName = Me.primaryKeyControl' in the Form_Current event.
