MS-Access / Getting Started

---

Use Exclamation Points and Periods

You've probably noticed that a complex, fully qualified name of an object or a property in Access 2010 or Visual Basic contains exclamation points (!) and periods (.) that separate the parts of the name.

Use an exclamation point preceding a name when the name refers to an object that is in the preceding object or collection of objects. A name following an exclamation point is generally the name of an object you created (such as a form or a table). Names following an exclamation point must be enclosed in brackets ([ ]) if they contain embedded blank spaces or a special character, such as an underscore (_). You must also enclose the name of an object you created in brackets if the name is also an Access or SQL reserved word. For example, most objects have a Name property-if you name a control or field "Name," you must use brackets when you reference your object.

To make this distinction clear, you might want to get into the habit of always enclosing in brackets names that follow an exclamation point, even though brackets are not required for names that don't use blank spaces or special characters. Access automatically inserts brackets around names in property sheets, design grids, and action arguments.

Use a period preceding a name that refers to a collection name, a property name, or the name of a method that you can perform against the preceding object. (Names following a period should never contain blank spaces.) In other words, use a period when the following name is of the preceding name (as in the TableDefs collection of the Databases(0) object, the Count property of the TableDefs collection, or the MoveLast method of the DAO Recordset object). This distinction is particularly important when referencing something that has the same name as the name of a property. For example, the reference

DBEngine.Workspaces(0).Databases(0).TableDefs(18).Name

refers to the name of the 19th TableDef object in the current database. In the Contacts. accdb database, if you use Debug.Print or the Immediate window to display this reference, Visual Basic returns the value tblCompanies. However, the reference

DBEngine.Workspaces(0).Databases(0).TableDefs(18)![Name]

refers to the contents of a field called Name (if one exists) in the 19th TableDef object in the current database. In the Conrad Systems Contacts database, this reference returns an error because there is no Name field in the tblCompanies table.

You can use SetTempVar, RemoveTempVar, and RemoveAllTempVars actions to create, modify, and inspect values that you can pass from one macro to another. If you create an application that uses both macros and Visual Basic, you can also create, modify, and inspect these variables by using the TempVars collection. Unlike most collections in Access where you must first create an object before you can reference it, you can both create and set a macro temporary variable by simply assigning a value to a name in the TempVars collection. For example, to create and set a temporary variable called MyTempVar, use the following:

TempVars!MyTempVar = "Value to pass to a macro"

Temporary variables are the Variant data type, so you assign a string, a number, or a date/time value to a member of the TempVars collection. To delete a temporary variable, use the Removemethod as follows:

TempVars.Remove MyTempVar

To remove all temporary variables, use the RemoveAll method as follows:

TempVars.RemoveAll

However, be careful. If you reference a temporary variable that does not exist yet, you won't get any error. If you misspell a temporary variable name, Access temporarily creates the variable and returns the value Null.