Methods for Referring to Objects

Bang (!) verses Dot (.) verses Quotes ("")

In general, you follow the bang with the name of something you created: a form, report, or control. The bang also indicates that the item to follow is an element of a collection. You'll usually follow the dot with a property, collection, or method name. Actually, under the covers, the bang separator really says, “retrieve the following object from the default collection of the parent object.”

One place where the bang operator is necessary is in query parameters that refer to form fields. That is, you cannot avaoid the Forms!FormName!ControlName syntax in this case.
Quotes allow you to use a string variable in place of the object name
It is generally considered to be bad practice to use the bang in VBA code.

Examples:

Set ctl = Forms("frmTest").Controls("txtName")

' since Controls is the default collection of a form, you can abbreviate the above line as either of the following:
Set ctl = Forms("frmTest")("txtName")
Set ctl = Forms("frmTest")!txtName

' bang vs quotes - the following statements are the same
cat.Tables!tblCustomers.Columns!Address.Type
cat.Tables("tblCustomers").Columns("Address").Type

Syntax	Details	Example
collection(“name”)	n/a	cat.Tables(“tblCustomers”).Fields(“Street Number”)
collection(var)	Where var is a string or variant variable

strTable = “tblCustomers” <br />strField = “Street Number” <br />cat.Tables(strTable).Fields(strField) </html>|

collection(ordinal position)	Where ordinal position is the object's position within its collection	cat.Tables(0).Fields(0)

collection!name <br />collection![name] </html>|Brackets are necessary if name contains a nonstandard character, such as a space |cat.Tables(“tblCustomers”).Fields![Street Number] |