'O hateful error, melancholy's child!
Why dost thou show, to the apt thoughts of men
The things that are not?'

Shakespeare, Julius Caesar (1599)

Form and Report Text Boxes Show #Error


Question

I use expressions as the Control Source for text boxes on forms and reports. Some of these work OK, but others throw #Error. I cannot see why. Do you know?


Applies To

This topic is relevant to Microsoft Access 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013 and 2016.


Answer

The most common reason we find is that the name of the text box control itself is the same as the name of a field in the record source of the form, or of the report, that bears the text box.

By way of illustration, imagine a form or report based on a table that has a field named 'TelNo'. Also, that a text box on the form or report 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 the error.

Change the name of the text box to 'tebTelNo', say, and the Control Source expression to

="Telephone No: " & [tebTelNo]

and the text box should behave.


Disclaimer

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.