How to Fix Text Box Control #Error on Microsoft Access Form or Report
How to fix a text box on a Microsoft Access form or report that is displaying #Error.
Updated by David Wallis on 2020-06-06.
“I use expressions as the Control Source for text boxes on forms and reports. Some of these work OK, but others display ‘#Error’. I cannot see why. Do you know?”
Whether form or report, I find the most common cause of the error is when the Name property of the text box control itself is the same as the name of a field assigned to the text box’s Control Source property.
Imagine a form based on a table that has a field named “TelNo”. Also, that a text box on the form is named “TelNo”. For the Control Source of the text box, you input
="Telephone No: " & [TelNo]
Now Access cannot make up its mind between TelNo the field and TelNo the text box, and as a result displays “#Error”.
Change the Name of the text box to “tebTelNo”, say, and now the expression above will work. So will this expression:
="Telephone No: " & [tebTelNo]
Whichever expression you apply, the text box should behave, displaying for example, “Telephone No: 01732 833085”.
Access Naming Conventions
The example on this page of an error arising out of lack of consideration for properly naming objects is but one reason why I recommend you apply rigour to the design of forms and reports.
Conventions for Naming Objects in Microsoft Access Databases is my guide to adopting names for objects to help you in your work as a database developer.
If you need a general guide to designing forms, then please see my Designing and Creating Forms in Microsoft Access article.
Solution Applies To
This topic is relevant to Microsoft Access 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013, 2016, 2019 and Microsoft 365.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
If you’d like an invoice to account for your donation, let me know how much you’re donating —
Thanks, in anticipation.
DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.