Microsoft KB Archive/112130

= ACC: Using "Name" in Expressions =

Article ID: 112130

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q112130



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
All Microsoft Access version 2.0 objects have a Name property, but Microsoft Access version 1.x objects do not. A Microsoft Access 1.x database does not run correctly in Microsoft Access 2.0 if you refer to Name as an ambiguously qualified object in the 1.x database. To correct this, Microsoft Access treats Name as a special case when you are using a 1.x database.



MORE INFORMATION
With the advent of Microsoft Access 2.0 and data access objects (DAO), all objects have a Name property. Name is also a very common field name. This creates a conflict when you are running a 1.x database in Microsoft Access 2.0. When you refer to an ambiguously qualified object, both Microsoft Access 1.x and 2.0 have a predetermined search path to locate the object. The search order is as follows:

  Properties, controls, fields

If a field has the same name as a property, the value of the property is returned instead of the value of the field. In order to deal with the new Name property, the search order when referring to Name changes to:

  Controls, fields, properties

Note that this only applies to Name and not to any other property.

Listed below is acceptable ambiguous syntax in Microsoft Access 1.x and unambiguous syntax in Microsoft Access versions 1.x and 2.0. Microsoft Access 2.0 handles the ambiguous syntax correctly when you are using an Access 1.x database.

Acceptable (Ambiguous) 1.x Syntax
  In a text box on a report:

     ControlSource: =[Name]   In a macro:

     SetValue Item: [Name] Expression: UCase([Name])   In a DLookup expression:

     DLookup("[ID]","MyTable","[Name]=Form.Name")   In Access Basic using ListTables, ListFields, and so on:

<pre class="fixed_text">     Set MySnap = MyDB.ListTables Debug.Print MySnap.Name </li></ul>

Unambiguous 1.x and 2.0 Syntax
<ul>  In a text box on a report:

<pre class="fixed_text">     ControlSource: =Report![Name] </li>  In a macro:

<pre class="fixed_text">     SetValue Item: Form![Name] Expression: UCase(Form![Name]) </li>  In a DLookup expression:

<pre class="fixed_text">     DLookup("[ID]","MyTable","[Name]=Form!Name") </li>  In Access Basic using ListTables, ListFields, and so on:

<pre class="fixed_text">     Set MySnap = MyDB.ListTables Debug.Print MySnap!Name </li></ul>

Note the explicit use of the form or report object and the use of the exclamation point (!) instead of the period (.) to refer to fields. This tells Microsoft Access to search controls and fields and to ignore properties. If you use the period (.), properties are assumed and searched before controls and fields.

Using Naming Conventions
Ambiguously referenced objects can also be correctly referenced by using a naming convention that includes a standard prefix or suffix that can be attached to object names. For example, ctlName could indicate a control, and fldName could indicate a field.

Converting to Microsoft Access 2.0
When you are converting a Microsoft Access 1.x database to 2.0 format, you must use the unambiguous method to reference fields and controls that have the same names as properties.

<div class="references_section">