difference between ! and . 3

[scottian]

can anyone tell me the difference between usin a '!' and a '.' in vba within access?
for example:-
Me![Field1] = 12
and
Me.[Field1] = 12

 

[combo]

With "!" you reference objects in a collection. With "." you refer to properties and methods. Sometimes members of a collection are also properties (like form's controls). In this case both ways of referencing work. When the member is not a property of the object - dot fails.

combo

 

[TonyJollans]

Hi scottian,

Very simply, dots (".&quot are used to denote Properties and Methods of an Object, and bangs ("!&quot are used to denote Members of Collections.

Controls on a Form are both members of the Controls Collection AND Properties of the Form. The two different syntaxes represent two different routes to the same object.
Me![Field1] is shorthand for Me.Controls("Field1&quot
Me.[Field1] refers to the Field1 Property.

If your control name contains a space, the Form's Property has the spaces replaced with underscores so Me![Field 1] and Me.[Field_1] both refer to the control called "Field 1".

It is marginally more efficient to use the Properties but also preferable because they appear in the Intellisense and are validated at compile time rather than run time.

Enjoy,
Tony

 

[scottian]

thanks for that. much appreciated

 

[danvlas]

Explanations were perfect.

Now about using notations:

I would use
Me("Field1&quot

rather than
Me![Field1]
or
Me.[Field1]

Simply because the first way of referring to a control/field allows VB variables:

Function SetAValue(FormName, ControlName, SomeValueValue)
Forms(FormName)(ControlName) = SomeValue
End Function

Can't use the other notations to do that.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[xlbo]

What [ ] does is to EVALUATE whatever is inside it and that evaluation (for objects rather than variables) causes the DEFAULT property of the object to be used so for excel
[range] will return the VALUE of the range as that is the range default property
[Field] in access, would return the default property of a field (which I guess is the value again)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[danvlas]

Well, I believe that is the normal behaviour of any notation:

Me.Field1
Me!Field1
Me("Field1&quot equivalent to Me.Controls("Field1&quot
Me![Field1]

will all return the default property of the field, which is indeed the value.

The brackets (in Access) are only needed if you have spaces in the field name(which is anyway highly unrecommendable):

Me![Field 1]

I saw somewhere on Tek-Tips a post saying that what is in brackets is evaluated as a string and I agree with that.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[xlbo]

Not sure about Access but for excel - from the evaluate method help:

Note Using square brackets (for example, "[A1:C5]&quot is identical to calling the Evaluate method with a string argument. For example, the following expression pairs are equivalent.

[a1].Value = 25
Evaluate("A1&quot.Value = 25


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[combo]

Let's keep in mind the difference between object and its default property, in excel [A1] is still range:
x=[A1] returns value in "A1",
Set y=[A1].Offset(1,0) returns range "B1".
The idea with [ ] in excel and access is probably the same - it returns object with name or identifier in between (however, with formula, it can also return calculation result).

When returning an item from a collection, as Item is the default property of collection, we can use:
coll1.Item or coll1 for short.
When the collection has named items:
coll1.Item("obj_name&quot or coll1("obj_name&quot for short
or with "!" operator:
coll1!obj_name or coll1![obj_name] if name consists spaces.
This is universal.

combo

 

[TonyJollans]

Hi All,

I've never been entirely sure of the full effect of using brackets in Access, but here, briefly, is my best shot.

Dan is right to say they are needed when a field name contains spaces but, more generally, they force an interpretation process which may differ from the default in that Access always tries to interpret the string inside the brackets as a literal (usually a column name) first. For this reason, a column name the same as a reserved word (e.g. Date) usually needs putting in brackets; and an identifier which could, in context, be a variable name needs to be in brackets if it is to be treated as a Field Name.

Enjoy,
Tony