"." v "!"

[DaveJHPT]

I've seen numerous posts where posters are recommended to use one or other - I've always just used whatever felt right and never had any problems. What exactly are the pitfalls of using the wrong one?

Dave

 

[Mile-O]

The dot syntax (called early binding) is the preferred method as this allows you the use of the IntelliSense drop downs in the editor and lets the compiler explicitly know what is being referred to.

The bang syntax (called late binding) does not allow you the use of the IntelliSense drop downs in the editor and any errors you make with it will not become apparent until runtime when something is reference which doesn't actually exist.

Beginning with early binding we could use the following line:

Dim strExample As String
strExample = Me.txtExample

By using the dot we get a list and can select the textbox txtExample from it. By selecting this we can ensure there are no mistakes and that txtExample does indeed exist within the form's class.. When the code is compiled it is known that the code is referring to the textbox. If there is no txtExample then you will be informed of the error when the code is compiled.

Taking the same example in late binding:

Dim strExample As String
strExample = Me!txtExample

When this code is running the computer has to work out what txtExample is. It will cycle through all the properties and methods until the appropriate one is found. If there is no textbox called txtExample then the error will trigger at runtime and will be undetected by the compiler.

When coding, try always to be explicit.

You may see the following when dealing with recordsets (and you see it a lot here)

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblEmployees")
rs.AddNew
rs!Forename = "John"
rs!Surname = "Smith"
rs.Update
rs.Close
Set rs = Nothing

Two things here.

1. There is no consistency. One minute the programmer is using early binding and then switches to late binding. Be consistent!
2. As the fields need to be reference at runtime the interpreter has to cycle through all the recordsets properties and their subsequent properties to find the fields called Forename and Surname in the hope of having a match.

The following is more appropriate:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblEmployees")
rs.AddNew
rs.Fields("Forename") = "John"
rs.Fields("Surname") = "Smith"
rs.Update
rs.Close
Set rs = Nothing

So, the rule (or best practice, at least) is to only use late binding where early binding cannot be achieved. An example of this is in a query when you want to refer to a form. i.e. [Forms]![MyForm]![MyTextBox]

 

[ColinEssex]

Nice one Mile-O, to be honest, I was never 100% sure but now its quite clear.

Col

 

[DaveJHPT]

Ah, so that's it.

Thanks for the explanation, SJ - clear as ever!

Dave

 

[Pauldohert]

Is there a difference in timed performance?

 

[maxmangion]

this is an extract from a book regarding the ! and .

There seems to be much confusion over when to use the bang operator (!) and when to
use the dot operator (.). Perhaps the following will help:
• The bang operator is used to separate an object’s name from the name of the
collection of which it is a member. In other words, bang signifies a member of a
collection. It therefore appears in semiqualified object names.
• The dot operator is used to separate each semiqualified object name in a fully
qualified object name. In other words, it signifies the next step in the hierarchy.
• The dot operator is also used to denote a property or method of an object.

 

[Mile-O]

Is there a difference in timed performance?

Just timed them there. The results are:

Early binding was faster on two occasions and Late was faster on one occasion.

Early binding was faster when:

The field's Index was referred to:

rs.Fields(1)

And a compiled constant was used as the field's name:

Const MyField As String = "FieldName"
rs.Fields(MyField)

Late binding was faster when:

The field name was given as a string:

rs.Fields("FieldName")

This was based on inserting a record into a table 10,000 times. The difference was milliseconds so the issues is not speed.

With late binding, however, typos can't be detected until runtime.

 

[namliam]

With both methods (if I am right)
rs![FieldName] (with or without the [])
and
rs.Fields("Fieldname")
typos are not detectec till the code is run and finds it....

 

[Mile-O]

True, but at least it's explicit in noting that it's a Field.

 

[ghudson]

Excellent explanation SJ McAbney!

 

[The_Doc_Man]

One more comment about why bang (!) is right in

[Forms]![MyForm]![MyTextBox]

The collection Forms doesn't exist unless a form is open. It a dynamic collection. So you cannot bind early. No guarantee that it exists. If any readers were wondering, when all forms are closed, they are members of the Documents collection, which is static, but you cannot see inside it. You can open a form from VBA to step through its controls and sections, ditto for a report, but you cannot see those things from a closed document. Therefore, the "." syntax, seeking something defined at compile time, cannot see anything in [Forms] ... or [Reports], for that matter. Same principle.

 

[dt01pqt]

With both methods (if I am right)
rs![FieldName] (with or without the [])
and
rs.Fields("Fieldname")
typos are not detectec till the code is run and finds it....

Yes any collection properties with an index are late not just the bang. It would be the same for rs.Fields(2) etc. It is your responsibly to check indexes and indexes are dynamic and can be resorted. In a collection every index is paired with a name, e.g. index=name. But when resorted the top index is always 0 then 1,2,3, etc.

 

[KenHigg]

Then there's the quote stuff -

forms("myForm")("myTextBox")

Helpfull for when you need to dynamically build the control names you want to reference -

Link