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 2021-02-05.
“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.
How might this situation have arisen?
Imagine you’re creating a form based on a table that has a field named “DateInvoice”. From the Field List of the form you’re designing, you drag the “DateInvoice” field onto the form. This creates a text box, and in so doing gives that text box the name “DateInvoice”.
So, now the form has an object and a field in its record source, both named “DateInvoice”.
Now Access cannot make up its mind between DateInvoice the field and DateInvoice the text box. This can be the cause of other controls displaying “#Error”, as you’ve experienced in your forms and reports.
Change the Name of the text box to “tebDateInvoice”, say, and now you won’t cause Access any possible confusion.
If you want a new text box to display the due date of the invoice, then the Control Source of this new box could be one or either of these expressions:.
=DateInvoice: + 30
=tebDateInvoice: + 30
Whichever expression you apply, the new text box should behave. If the error persists, then you’ll need to search for other causes. But at least you’ll have eliminated this one.
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) —
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.