http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx
Should I use a bang or dot?
One of the questions frequently asked by new Access users (and veterans as
well) in the newsgroups concerns writing expessions that reference various
collections, objects, and properties. When should you use a bang (!) and when
should you use a dot (.) to reference the various relationships? For example,
suppose that you are writing VBA code and you want to reference a control on an
open form. The long version of the expression to refer to the same control is:
Forms("MyForm").Controls("MyTextbox"). The short answer is:
Forms!MyForm!MyTextbox.
Why is this so? The bang character (or parenthesis with quotes) separates an
object from the collection it's in (a field in a table, a form in the Forms
collection, a control on a form). The bang indicates that the item to follow is
an element of the collection. The bang separator says "retrieve the following
object from the default collection of the parent object." It is primarily used
to replace the collection designator such as the Controls keyword in order to
reduce the number of words needed to describe the reference. So
FormName!ControlName is shorthand for FormName.Controls!ControlName.
The dot on the other hand is used to separate an object from a property,
collection, or method of that object. When working with forms, you can refer to
a control on the form as: Forms("MyForm").Controls("txtName")
Because Controls is the default collection of a form,the shorthand is either of
the following:
Forms("MyForm")("txtName")
Forms("MyForm")!txtName
This too saves a few keystrokes. You'll usually follow the dot with a property,
collection, or method name, as you'll see shortly. In addition, as an
alternative to the bang character, you can also use parentheses and quotes. The
following two statments refer to the same property:
Catalog.Tables("tblCustomers").Columns("Address").Type
Catalog.Tables!tblCustomers.Columns!Address.Type.
Shortly, you'll also see why the parenthesis and quotes are better to use than
the bang. Notice that the Columns collection and the Type property follow the
dot.
One of the nuggets of wisdom you'll often hear is that "dots are used before
things created by Access, while bangs are used before things created by you."
This is not a hard and fast rule and has exceptions. For example, the bang
operator is required in query parameters that refer to form fields. A better
rule of thumb is to use bangs because they are identified as members of a
collection and not because you created them. FormName!ControlName is short for
FormName.Controls!ControlName. The Controls collection can be left out because
it is the default property of the form. Again the dot is used before
collections, properties, and methods.
One of the advantages of using the dot in VBA is that unless there is a bang
operator earlier in the expression, you get an Intellisense drop-down showing
all of the properties you can choose from. So, you can type Me. and pick from
the list. Using dots rather than bangs also makes your code run a bit faster.
One of the downsides of using a dot is that you may run into trouble if your
control or field has the same name as one of the built-in properties of a form.
This is not a recommended practice.
The main advantage to using the bang character is that it saves you some key
strokes. However, it also has the disadvantage of causing a speed penalty. Why?
Earlier we said that it was better to use parentheses and quotes than the bang.
Behind the scenes, Access translates the dot-and-bang reference to the
parentheses and quotes style anyway. As a result, while the bang will save you
a few strokes, the translation will incur a performance penalty.
Thus the recommendation is to always use the parentheses and quotes styles when
referring to a member of a collection except where you must use the bang (such
as in query parameters that refer to form fields) .
Additionally, if the name of the object has spaces, you'll need to surround the
name in square brackets.
Published Wednesday, February 18, 2004 1:43 PM by frice
