ACC: Using "Name" in Expressions (112130)

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:
  

        Set MySnap = MyDB.ListTables()
        Debug.Print MySnap.Name
  						

Unambiguous 1.x and 2.0 Syntax

• In a text box on a report:
  

        ControlSource: =Report![Name]
  						

• In a macro:
  

        SetValue
           Item: Form![Name]
           Expression: UCase(Form![Name])
  						

• In a DLookup() expression:
  

        DLookup("[ID]","MyTable","[Name]=Form!Name")
  						

• In Access Basic using ListTables(), ListFields(), and so on:
  

        Set MySnap = MyDB.ListTables()
        Debug.Print MySnap!Name
  						

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.