Others

How to Make an Access Text Box Editable with an IIF Condition

Adam Davis Oct 9, 2026 868 views

Question details

The user wants to conditionally allow data entry in a text box based on a Yes/No field, automatically filling 'N/A' when disabled, but found that using an IIF expression prevents manual edits.

How to Make an Access Text Box Conditionally Editable

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a data entry form where specific text boxes should only be editable if a related Yes/No condition is met.
Observed behavior: Placing an IIF expression directly in the text box's Control Source calculates the expected value but turns it into a calculated control, locking the field and preventing manual text entry.

Before you start

Before adding VBA code to your form, ensure your database is saved in a trusted location and that macros are enabled in your Microsoft Access Trust Center settings.

Solution 1Recommended

Use VBA Form Events to Lock and Unlock Controls

Instead of using a read-only IIF expression in the Control Source, use VBA in the After Update and Form Current events to control the text box state dynamically.

When you place a formula like an IIF expression into the Control Source of a text box, Microsoft Access treats it as a calculated control, making it strictly read-only. To achieve conditional editing, you must leave the Control Source bound to your table field and use VBA code to handle the logic.

Remove the IIF Expression

Open your Access form in Design View. Select your text box, open the Property Sheet, and remove the IIF expression from the Control Source so it binds directly to the correct table field.

Add the After Update Event

Select the Yes/No checkbox control (e.g., Cell_Phone). In the Property Sheet, go to the Event tab, click the builder (...) next to 'On After Update', choose Code Builder, and write an If/Else statement to set the text box values to 'N/A' or Null based on the checkbox state.

Create a Lock/Unlock Subroutine

In the same VBA module, write a private subroutine (e.g., Private Sub LockUnlock()) that checks the checkbox value and sets the 'Locked' property of your text boxes to True or False accordingly.

Call the Subroutine on Form Current

Select the entire Form in Design View, go to the Event tab in the Property Sheet, and click the builder for 'On Current'. Call your LockUnlock subroutine here to ensure the text boxes are correctly locked or unlocked as you navigate through different records.

Use VBA Form Events to Lock and Unlock Controls

Database Normalization: If you are tracking multiple phone numbers per user, consider a relational database design using a separate 'Phone Numbers' table and a subform, rather than creating multiple fixed columns on a single form.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite? Try WPS Office

While Microsoft Access handles complex database management, your daily document, spreadsheet, and presentation tasks can be effortlessly managed by WPS Office. It provides a robust, lightweight, and free alternative to Microsoft Office.

1. Visit the Official Website: Go to wps.com to download the free installer for your operating system.
2. Install WPS Office: Run the lightweight installation package and follow the quick on-screen instructions.
3. Open Your Documents: Launch WPS Office and seamlessly open, edit, and save your existing Office files without losing formatting.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight installation that runs fast and smoothly on older and newer devices.Familiar, tabbed user interface making migration seamless and incredibly easy.Built-in PDF editing tools for comprehensive and fast document management.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I edit an Access text box that contains an IIF statement?

When you use an IIF statement or any mathematical formula in the Control Source of a text box, Access defines it as a calculated control. Calculated controls are inherently read-only because their values are dynamically generated by the expression, not stored directly in a table.

How do I disable a text box in Access without using VBA?

You can manually set a text box to be read-only by opening the Property Sheet in Design View, navigating to the Data tab, and changing the 'Locked' property to Yes. However, making it toggle dynamically based on another field's input requires VBA.

What is the purpose of the Form Current event in Access?

The Form Current event triggers whenever the focus moves to a record, making it the active record. It is essential to use this event when dynamically locking or unlocking controls so that the form reflects the correct state based on the specific data of the record you are currently viewing.

Office Quick Solutions