I see #Error displayed in a control

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

#Error displayed in a control

Symptoms

You see #Error displayed in a control.

Cause

Access displays #Error in a control when it cannot evaluate the expression that supplies the control's value. You use the ControlSource property to supply the source of the control's value. For example, if you supply an expression for the ControlSource that includes a function, and there are too few arguments supplied to the function, or if the arguments you supply are incorrect, Access displays #Error in the control.

You'll also see #Error displayed in a control when a name conflict occurs that creates what is called a circular reference. A circular reference occurs when you refer to a control from within its own ControlSource property. Most often, this occurs after you drag a field from the field list to a form or report, and later decide to change its ControlSource property from a field name to an expression.

If you reference a subreport control from a main report and the subreport has no data, you may see #Error displayed.

If your DefaultValue property setting is not appropriate for the field's or control's data type or field size, you may see #Error displayed. For example, if a text field has a field size of 2 and you set the DefaultValue property to a three-character string, you may see #Error displayed.

When a calculated field results in a value that violates the FieldSize property setting, you may see #Error displayed in a control.

If you have control names or field names that include spaces and you forget to enclose the entire name within brackets, Access may display #Error.

If you refer to controls or fields that don't exist, or if you refer to controls or fields incorrectly, you may see #Error displayed.

If your database is missing a Component Object Model library reference, you may see #Error displayed.

Resolution

  • Check function arguments and punctuation    

If you're using an expression that contains a function, check that the number of arguments you supply to the function is correct. Also check that you supply the arguments in the correct order, and that you've included any necessary punctuation. For assistance with determining the correct number and type of arguments, see the Help topic for the particular function.

  • Check for a name conflict    

You may have a name conflict. When you drag a field from the field list onto a form or report, Access creates a new control and gives it the same name as the field (as long as a control with that name doesn't already exist).

Dialog box showing identical names

If you then change the ControlSource property of the control to an expression that references the same name, you've created a circular reference. Access cannot evaluate the expression because it does not know whether you are referring to the field or the control, which both have the same name.

Dialog box showing circular reference

As a rule, whenever you have a control whose name is the same as a field name, change the Name property of the control so that the name is not the same as a field name.

You might also experience a circular reference if you have two controls that specify each other in their control source. In this case, you must remove the circular reference so that only one control is dependent on the other.

  • Guard against subreports that have no data    

If you see #Error displayed when you reference a subreport that has no data, consider employing the HasData property of the subreport. For example, you could wrap your existing expression within an IIF (immediate if) function that returns 0 when no data exists and the correct value otherwise. Here is an example:

=IIF([Sub].Report.HasData=True, [Sub].Report!Amt, 0)

This function tests the first condition, which uses the HasData property to determine whether the subreport has any data — the subreport is identified by [Sub] in the preceding example. Use whatever name you assign to the subreport control in the main report. If the subreport does contain data, the function returns the value from the Amt control of the subreport. Otherwise, it returns 0.

  • Place brackets around names with embedded spaces    

If you are referring to control names or field names that include spaces, be sure that those names are enclosed within brackets.

  • Check that named controls exist and that you refer to them correctly    

If you referring to another control, make sure that the control exists and that you are referring to it correctly.

  • Check library references    

Make sure that your database is not missing a reference. A reference is the means by which Access makes COM (Component Object Model) object libraries available for use within Access. If you use automation to run other Office programs from Access, for example, you might be missing a reference to the other Office application's COM libraries. To check references, take the following steps:

  1. In the Database window, click Modules.
  2. Click any module, and then click Design.
  3. On the Tools menu, click References.
  4. In the References dialog box, clear any check box for a type library or object library marked as MISSING: <referencename>.

An alternative to removing the reference is to restore the referenced file to the path that is specified in the References dialog box. If the referenced file is in a new location, clear the MISSING: <referencename> reference, and then create a new reference to the file in the new folder.

  • Check that the function works in a data access page    

If the control is in a data access page and contains an expression that uses a function, make sure that the function is available for use in that data access page. Because data access pages use VBScript, you can only use functions that are available in VBScript. To find a list of VBScript functions, display the Functions reference in the VBScript Language Reference in VBScript Help.

  • Check that the field size is large enough    

If the expression in your Default Value property results in a value that is larger than that allowed in the field size, or is not appropriate for the field's data type, you must either change the expression, increase the field size, or change the data type. If you have a calculated field that results in a value that is too large for the field's data type, you must change the calculated field expression.

For more information

For additional information regarding specific issues that result in #Error appearing in a control, see the following Microsoft Knowledge Base articles:

 
 
Applies to:
Access 2003