Bang (!) versus Dot (.)

In Access VBA, references to objects and their properties or methods sometimes use the bang (!) character between names, and sometimes they use the dot (.) character. This leads people to ask, what's the difference between the bang and the dot, and which is proper syntax?

It's not so much a question of one or the other being "proper syntax", but that they mean different things that nevertheless almost always give the same result. The bang (!) notation specifically denotes that what follows is a member of a collection. In the case of a reference such as Forms!FormName!ControlName, "FormName" is a member of the Forms collection, and ControlName is a member of that form's default collection, the Controls collection. If you were writing code for the code module of form "FormName", you could write "Me!ControlName".

The dot (.) notation denotes that what follows is a property or method of the preceding object. So, for example, a reference to the Caption property of a label control on the current form might be constructed as "Me!LabelControlName.Caption".

This understanding would logically make the bang notation "proper" for reference to controls on forms, and the dot notation improper.

But wait. Wherever possible, Access makes the controls on a form and the fields in its recordsource all available as properties of the form. It also makes the fields of the recordsource available via the bang notation. I'm not sure exactly how it does this; maybe if a name is not found in the Controls collection it checks the Fields collection of the form's recordset as a fallback position. So for most practical purposes Me!ControlName and Me.ControlName evaluate to the same thing, and the timing tests I've seen suggest that there is little to choose between them as far as execution efficiency is concerned. I seem to recall that there is a very slight difference, but I can't remember which way the advantage lies, and it's not much. There's a coding-time advantage, however, to using the dot notation, as it makes the "intellisense" dropdown lists available. That's a strong argument for using the dot notation, in my book.

But wait again! I said above that Access makes the controls available as properties "wherever possible". There are cases where it can't do that. Specifically, it can't do it when there is already a property of the same name as the control in question. For example, if your form "Form1" has a control or a field foolishly named "Name", currently displaying the value "John Doe", then executing this statement in the form's code module:

Debug.Print Me!Name, Me.Name

will print

John Doe     Form1

in the Immediate Window. So you must be careful not to use any reserved words or built-in properties as names for your controls, if you want to use the dot notation to refer to them. But then, you should avoid doing that anyway, as it tends in general to confuse poor Access.