Others

How to Disable One Access Field When Another Has a Value

Khadija KhanKhadija Khan Nov 2, 2026 868 views

Question details

Configure an Access 2016 form so that entering a value in one specific field automatically clears and disables another mutually exclusive field.

How to Disable One Access Field When Another Field Has a Value
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Form Design View

Right-click your form in the Access navigation pane and select 'Design View'.

2
Configure the 'After Update' Event

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.

3
Add Logic to Clear and Shift Focus

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.

4
Disable or Lock the Second Field

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.

5
Repeat for the Inverse Field

Apply the exact opposite logic to the 'After Update' event of your second field (Assisted_Amount), disabling the first field (Amount).

6
Update the 'On Current' Event

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).

Use VBA Form Events to Disable or Lock Mutually Exclusive Fields
Handling Default Zero Values: If your database fields have a default value of zero instead of Null, test for <> 0 instead of using IsNull() in your VBA conditional statements.
Free Microsoft Office alternative

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. 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
  2. 2. Install WPS Office: Run the installer and follow the on-screen instructions to set up Writer, Spreadsheet, and Presentation.
  3. 3. Open Exported Data: Export your Access tables or queries to an XLSX file, then open them instantly using WPS Spreadsheet for quick analysis.
Seamless compatibility with Microsoft Word, Excel, and PowerPoint formatsFree and lightweight, taking up minimal system resourcesFamiliar tabbed interface for a zero-learning-curve transitionBuilt-in PDF editing tools for comprehensive document management
microsoft office alternative - wps office

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.