FIXED: "You entered an expression that has no value."

Have you ever opened a form or report after making a minor change to a text box's Control Source property and been greeted with the following error message?

Run-time error '2427':

You entered an expression that has no value.

The most likely reason for this error is that you've inadvertently created a circular reference in a text box's Control Source.

MCVE: Minimal Complete Verifiable Example

The gold standard when trying to reproduce an error is to define a "minimal complete verifiable example" of the bug.  Not all bugs lend themselves to an MCVE, but it should still be your goal as a developer to create one, if possible.

Here are the steps to generate an MCVE for this error message:

1. Create a new blank database
2. On the Create tab, click [Form Design]
3. In the Form's Record Source property enter MSysObjects (if necessary, press [Alt] + [Enter] to open the Property Sheet)
4. On the Form Design tab, click [Add Existing Fields]
5. Click and drag the DateCreate field from the Field List to the form's Detail section
6. Switch to Form View

At this point you should see the DateCreate text box displaying the value from the first row of the MSysObjects table.  Since you just created the database, the DateCreate value should be today's date:

So far, so good.  Now, let's break it.

Calling a Custom VBA Function

To generate the error message, we need to call a custom VBA function.  For simplicity's sake, we'll use my Watch() function.  It's a debugging procedure that simply returns whatever value is passed to it.

'source: https://nolongerset.com/watch-this/
Function Watch(Val, Optional CalledFrom As String = "")
    'Debug.Assert Val <> "Value to watch for"
    'Debug.Print Val, CalledFrom
    Watch = Val
End Function

1. Copy the above function and paste it into a new standard module
2. Switch the form back to Design View
3. Change the Control Source of the DateCreate text box from DateCreate to =Watch([DateCreate])
4. Switch the form to Form View

You should get the following error message:

What's going on here?

Circular Reference

One of the annoying default behaviors of Access is that when you add a control to a form or report from the Field List window, it creates a text box whose Name and ControlSource are both set to the field name.

• Name: DateCreate
• Control Source: DateCreate

Everything is still OK at this point.

But what happens if we want to pass the field name to a custom VBA function?

• Name: DateCreate
• Control Source: =Watch([DateCreate])

The problem is that Microsoft Access automagically creates form object properties for every control and every field associated with the form.  So, for our form, there is a DateCreate control and a DateCreate field.  If a form has a control and field with the same name, the control property takes priority.

So, when the Access database engine's expression service evaluates =Watch([DateCreate]), the reference to [DateCreate] resolves to the DateCreate control and not the DateCreate field.  This results in a circular reference: the function is infinitely recursive.  

Theoretically, the expression service evaluates this:

=Watch([DateCreate])

As this:

=Watch(Watch([DateCreate]))

And then as this:

=Watch(Watch(Watch([DateCreate])))

Ad infinitum (or at least until it runs out of stack space).

In reality, the expression service likely looks for this situation and flags it as invalid without actually performing the recursive calculation.

In fact, newer versions of Access bring this error to the user's attention by flagging it in Design View:

Access also provides you with the two possible fixes:

• Edit Control Name
• Edit the Control's Control Source Property

As well as showing you a "Help on This Error" option which brings you right to this page to explain what is going on.

Haha! Just kidding.  Clicking on Help on This Error shows you this:

That darn network connection!  Somehow, the internet works fine just before and just after I try to use the Access Help.

Fixing the Error

As the context-sensitive error-flagging tooltip shows, there are two approaches to fixing the error.  Note that these fixes also apply to older versions of Access, including those that do not automatically flag the error in Form Design View.

Edit Control Name

With this approach, we change the control name so that it doesn't conflict with the field name.

For example, we could change the name of the control from CreateDate to tbCreateDate.

By doing that, we give unique names to two form properties that previously shared the same name:

• CreateDate: one of the fields in the form's Record Source
• tbCreateDate: one of the text box controls on the form's Detail section

Personally, this is the approach I use.  

In fact, I don't even wait for these kinds of errors to pop up.  I proactively change the name of every control as I add it to my form, especially if I use the Field List to create the control.

I don't use Systems Hungarian in my code, but I have adopted a form of that naming convention for my form and report controls.  I use the following prefixes for my controls:

• tb: Text box
• cb: Combo box
• lb: List box
• lbl: Label
• chk: Check box
• btn: Command Button
• rect: Rectangle
• img: Image
• og: Option Group
• ob: Option Button
• tc: Tab Control
• sf: Sub Form
• sr: Sub Report
• tog: Toggle Button
• web: Legacy Web Browser Control
• edge: Modern Edge-based Browser Control
• pb: Page Break
• line: Line
• ax: ActiveX

Edit the Control's Control Source Property

Honestly, this approach only makes sense if you've got a typo in your control source or you really are referring to the wrong thing.  In the vast majority of cases, the best solution will be to simply rename the control as described above.

Feature Request

I would love to see the option to define control name prefixes by control type.  This would have to be a user-configurable option, since different developers use different naming conventions (e.g., cmd is another common prefix for command buttons).  This feature could be implemented in one of two ways:

As an Access Option

Go to File > Options > Object Designers > Form/Report design view.

Under the "[_] Always use event procedures" option, add another checkbox that is unchecked by default, that says:

[_] Use control name prefixes [Define...]

The [Define...] button would be disabled unless the box was checked.  Clicking on the enabled [Define...] button would open a form with a two-column list.  The first column would have a header of "Prefix" and the second column would have a header of "Control Type."  "Prefix" would be a column of empty text boxes where the user could define prefixes for all the form and report control types which would be listed in the second column.  Essentially it would look like my unordered list under the "Edit Control Name" section heading above.

As a Default Control Property

The other way to implement this feature would be to expose the Name property in the default control view of the Property Sheet.

For example, here's what the "Other" tab looks like for an existing Label control:

And here's what the "Other" tab looks like when we click on the Label control button in the ribbon's Controls group and the Default Label control is active.  The properties listed below are the only ones under the "Other" tab that can be set for a label control.  "Name", "Help Context Id", and "Tag" cannot be given default values.

I propose that we add either a "Name Prefix" property (which would be a clearer name, but could be a problem because it's not an existing control property) or expose the existing control "Name" property and treat its contents as though it were a "name prefix" property.

Official Feature Request on the Feedback Portal

I'll be honest, I'm completely jaded by the whole feature request situation since my top-ranked feature request simply disappeared as part of the site's automated cleanup process.  (It appears Microsoft added it back as a new request about a year after they unceremoniously deleted it, but it lost an awful lot of ground over that time.)

If someone likes this idea enough to post it on the feedback site, I will gladly upvote it (throw a link in the comments below).  As for me, I won't be wasting the time to do it myself.