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.