How to Fix an Access Dropdown Value That Is Not Saved
Question details
Users are unable to save a selected value from a combo box or dropdown menu in a Microsoft Access form.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Entering data using a combo box or dropdown list on an Access database form.
- Observed behavior
- The dropdown displays selectable values, but the selected value is not saved to the underlying table, even though other text boxes on the form might successfully accept data.
Ensure you have design permissions for the Access database and that the underlying table intended to store the data is properly configured to accept the data type of your dropdown values.
Bind the Form and Dropdown to the Correct Data Source
The most common reason a dropdown value isn't saved is that the form or the control itself is not bound to a table or field.
Every form has a Record Source property. If the Record Source is blank, the form is unbound. Even if the form has a Record Source, each control (like your dropdown) must be bound to a specific field through its Control Source property. If there is no Control Source, the selected data has nowhere to go.
Right-click your problematic form in the Access navigation pane and select 'Design View'.
Press 'F4' on your keyboard or click 'Property Sheet' in the Design tab on the top ribbon.
Select 'Form' from the selection type dropdown at the top of the Property Sheet. Go to the 'Data' tab and ensure the 'Record Source' contains the correct table or query.
Click directly on the problematic dropdown box. In the Property Sheet, navigate to the 'Data' tab, find 'Control Source', and select the specific field where you want the value to be saved.
Save Values Using VBA for Unbound Forms
If your form intentionally has no Record Source (an unbound form), the dropdown data must be saved manually using a VBA script.
Looking for a Lightweight Alternative for Your Office Tasks?
While Microsoft Access handles complex relational databases, much of daily data tracking, reporting, and list management can be efficiently handled using spreadsheets. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, featuring robust spreadsheet capabilities that support advanced data validation and dropdown lists.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet where you want to collect data.
- 2. Access Data Validation: Highlight the cells where you want to add a dropdown, go to the 'Data' tab on the ribbon, and click 'Validation'.
- 3. Configure the Dropdown: In the dialog box, choose 'List' under the Allow criteria, select your source data range, and click 'OK' to create an instant, reliable dropdown.
Frequently Asked Questions
Why does my Access combo box show options but not let me select them?
This usually happens if the form's 'Allow Edits' property is set to No, or if the Recordset Type is set to 'Snapshot' instead of 'Dynaset'. Check the form's Data properties in the Property Sheet to ensure data entry and editing are permitted.
What is the difference between Row Source and Control Source in an Access dropdown?
The 'Row Source' determines the list of selectable options that appear in the dropdown menu. The 'Control Source' determines which underlying field in your database table the selected value will actually be saved into.
Can I save a dropdown value without using a bound form?
Yes. If you are using an unbound form (where the form's Record Source is intentionally left blank), you must write VBA code triggered by an event—such as a submit button click or the dropdown's 'After Update' event—to manually execute an SQL statement that saves the data to your table.
