How to Disable One Access Field When Another Has a Value
Question details
Configure an Access 2016 form so that entering a value in one specific field automatically clears and disables another mutually exclusive field.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a data entry form where users are required to fill out only one of two alternative fields, such as 'Amount' and 'Assisted_Amount'.
- Observed behavior
- The user needs to enforce mutual exclusivity dynamically, preventing dual data entry by disabling or locking one text box when the opposite one contains a value.
Ensure you have Design View permissions for your Access form and are familiar with opening the VBA Editor to add basic 'After Update' and 'On Current' event procedures.
Use VBA Form Events to Disable or Lock Mutually Exclusive Fields
Control the behavior of the two text boxes by utilizing the After Update event for active entry and the On Current event for form record navigation.
To make two fields dynamically interact, you must apply logic that detects when a value changes. Since Microsoft Access does not allow disabling a control that currently has focus, your VBA code must first shift the focus to another control before changing the enabled or locked state of the active field.
Right-click your form in the Access navigation pane and select 'Design View'.
Select the first field (e.g., Amount). In the Property Sheet, navigate to the Event tab, click the ellipsis (...) next to 'After Update', and choose Code Builder.
Write VBA code to check if the current value is not Null. If true, clear the second field (e.g., Assisted_Amount), and use 'SetFocus' to move the cursor to a neutral control on your form.
After moving focus, set the second field's Enabled property to False (e.g., Me.Assisted_Amount.Enabled = False). Alternatively, set its Locked property to True to preserve the normal visual appearance while preventing edits.
Apply the exact opposite logic to the 'After Update' event of your second field (Assisted_Amount), disabling the first field (Amount).
Select the entire form, go to its 'On Current' event, and add expressions to enforce rules when navigating between existing records: Me.Assisted_Amount.Enabled = IsNull(Me.Amount) and Me.Amount.Enabled = IsNull(Me.Assisted_Amount).

Enhance Your Productivity with WPS Office Suite
While Microsoft Access manages your complex relational databases, you'll still need a robust tool to handle data exports, reports, and daily documents. WPS Office is a lightweight, fully compatible suite that effortlessly opens Excel files exported from Access.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install WPS Office: Run the installer and follow the on-screen instructions to set up Writer, Spreadsheet, and Presentation.
- 3. Open Exported Data: Export your Access tables or queries to an XLSX file, then open them instantly using WPS Spreadsheet for quick analysis.

Frequently Asked Questions
Why do I get an error when trying to disable a field in Access?
Microsoft Access throws an error if you try to disable or hide a control that currently holds the focus. You must use the .SetFocus method to move the user's cursor to a different, active control (like a 'Save' button or another text box) before changing the Enabled property to False.
What is the difference between Enabled and Locked properties?
Setting the Enabled property to False grays out the text box, giving users a clear visual cue that it is inactive. Setting the Locked property to True prevents the user from modifying the data, but the field retains its normal white background and visual appearance.
How do I clear the other field if they are required and cannot be Null?
If your mutually exclusive fields are marked as required and default to 0 instead of Null, you should adjust your VBA code to evaluate for <> 0 instead of checking for IsNull(). When the user enters a valid value, use VBA to explicitly set the other field's value to 0.




