Me! or Me. Bang or Dot
There is a lot of confusion about Me. vs Me! (Bang vs Dot) usage, and often what is posted on the Internet is wrong or incomplete. The following attempts to clarify this.
VBA provides two coding constructs Bang and Dot. Bang uses a "!" and Dot uses a "." Bang notation can be used in a very limited context. Bang notation does one and only one thing.
It causes the runtime to invoke the default member of an object and pass the name following the bang as a string argument.
So what does that mean?
1.
Default Member: Microsoft classes have default properties. Assume you use a collection. The default property is "Item", and because it is a default you do not have to write it.
someCollection.item(2)
can be written
someCollection(2)
Most controls have a default value property so
x = txtboxA.value
is the same as
x = textboxA
The default for a form is the Controls collection, the default for a recordset is the fields collection.
So I can write
Me!ControlName
or
RS!FieldName
Only because they are default properties.
A form also has other collections that are not the default property. Example the "Properties" collection
Me!SomeProperty
does not work because it is not the default property
Me.Properties("SomeProperty")
Does work
The first point is Me! works only with
DEFAULT Properties.
2.
Pass the name following the bang as a string argument: This is probably the whole reason for even having Bang. It passes that at runtime and not compiled. So at runtime
Me!SomeControlName
gets passed to the DEFAULT property as a string "SomeControlName" an that DEFAULT collection returns the correct member. This can be proved that this happens at runtime and not compile time. If you type
Me!someWrongControlName
It will compile, but at run time when it passes "someWrongControlName" it will fail.
3.
Do I need to use Me! in vba: In vba almost never. It is not required in vba (except in an rare circumstance listed in 4), but it can be used in vba as short hand.
RS.Fields("fieldName")
Can be shortened in Bang
RS!FieldName
(The fields collection is the default property of a recordset)
However since Fields is the default property you can drop it in dot notation so
RS.Fields("fieldName") can be shortened in Dot as well to RS("fieldName")
So the Bang really only saves you 3 characters (" ") vs !
Drawbacks of Bang
One problem with bang is that since it is run time you will lack intellisense.
Me.SomeControl pops up in intellisense and you get the correct name
Me!SomeCntrol does not come up in intellisense and this will fail at runtime since the name is incorrect
Another problem with bang is you can not use variable references
dim cntrlName as string
cntrlName = "txtOne"
msgbox me.controls(cntrlName) '
msgbox me!ctrlName
4.
When do I need to Use ! Bang: As explained this passes the name following the ! at runtime. This is my guess why this exists at all. In queries and expressions you have to use !. That is because those are not compiled. In a query and expression you can not write vba only use functions and default methods.
(There is actually one use in vba where you would have to use !. If at run time you set the recordsource of a form or report and do not have a control bound to a field then Me.FieldName will not work, but Me!FieldName will work. This is not something normally done IMO. See not 6 for further discussion on why this is. The reason Me.FieldName does not work in this case is because since the control was never added to the form in design time there is no dedicated property for that Field. It does exist in the collection. The other way in this situation using Bang would be Me.controls("FieldName")
5.
What is Me: Unless you are building custom classes or multiple instance of a form/report in VBA this is kind of hard to see. Most of the time in vba you are using a single instance of a form or report class. But classes are like cookie cutters where you can build multiple instantiations. Also 99% of the time you do not even need Me in either form. Again this is because within a class Me is the default
Me.SomeControlName
is the same as simply
SomeControlName
or
Me.SomeProcedure
Same as simply
SomeProcedure
Most of the time people use
Me simply for clarity because most of the time it is not needed.
If using bang you cannot drop the Me or the object qualifier in front of it
Me!txtBox1
you cannot do
!txtBox1
Lets assume we can open two instance of the same form (which we can). A forms module is a class module. Assume in the class there is a method
Public Sub MyCaption()
msgbox me.Caption
end sub
If I opened two instances of the same form then changed their caption and called the MyCaption method.
dim frm1 as New Form_SomeForm
dim frm2 as new Form_SomeForm
frm1.caption = "frm1"
frm2.caption = "frm2"
frm1.myCaption ' message box with "frm1"
frm2.myCaption ' message box with "frm2"
In other words ME refers to the current instantion of the object. This is hard to in Access because most of the time we work with just a single instantiation of form or report objects.
6.
An apparent exception: As stated the bang only works with the default member of a class and for a form or report the Controls collection is the Default. However if there isa field in a forms recordsource, it can be referenced like:
Me!FieldName
As stated the default of a form is the controls collection and there is not even a fields collection so this seemingly should not work.
(Below information provided by
@sonic8 )
However, what appears as an exception is not. These fields are actually added as "pseudo" controls in the controls collection.
Access automatically adds a control of type AccessField to the Controls collection of a form for each field in the current data source that does not have a "real" control of the same name already. (- This is done whenever the Recordsource of the Form changes. However, historically this did not work reliably in older Access Versions. In current versions (Access 2016+) it appears to be reliable. These "auto-controls" are not included in Controls.Count, but you can also reference them using Me.Controls("FieldName").
