Guys, as I always say, just declare and initialize your variable, then watch what's inside it in the Locals window.
Code:
Sub CheckForm()
Dim theForm As Form
Set theForm = Forms("MyFormName")
Stop
End Sub
BY THE WAY, if you want to know what's the default member of an object, open the object browser, search the object type and the cyan colored icon is your default member.
Anyway, the code above will put theForm variable in the locals window. If you click the plus (+) sign to the left, it will let you see its properties and methods. Let's suppose you have a textbox inside MyFormName, the name of that textbox is txtSomething. Then:
Since it's a Form object, by looking in the object browser, I can see that Controls is the default member. That means that a simple theForm! <--- is referring to theForm.Controls. Then these three are the same thing:
Code:
Debug.Print theForm!txtSomething
Debug.Print theForm.Controls("txtSomething")
Debug.Print theForm.Controls!txtSomething 'whoa
You see what I did there with the bang (!)? Controls is another object with a default member, which is Item. What the bang is doing is a shorthand of theForm.Controls.Item("txtSomething"), so all these four lines do the same:
Code:
Debug.Print theForm!txtSomething
Debug.Print theForm.Controls("txtSomething")
Debug.Print theForm.Controls!txtSomething
Debug.Print theForm.Controls.Item("txtSomething")
As of this moment, you should be aware that, given that txtSomething is a textbox, and, looking in the object browser the default member of a textbox object is Value, the previous four lines are shorthands of
Code:
Debug.Print theForm!txtSomething.Value
Debug.Print theForm.Controls("txtSomething").Value
Debug.Print theForm.Controls!txtSomething.Value
Debug.Print theForm.Controls.Item("txtSomething").Value
Try it out, which one is best? whatever floats your boat. In fact, .Application, .Properties, .Parent, all of these also allow us to reference a control, we can set ourselves in a massive Matryoshka doll torture like this:
Code:
' just watch where your variable takes you
Debug.Print theForm.Application.Application.Parent.Forms!MyFormName.Module.Parent.Properties!ForeTint.Parent.Controls!txtSomething.Name
And it will reference the very same thing. Now, let's go into the recordset object. Default member is Fields, according to the object browser. Oh, Me? Me refers to a form object. We already know form objects have Controls as default member. So... Me! is the same as Me.Controls. And any variable initialized as a recordset type will have Fields as its default member. So these will be equivalent:
Code:
Debug.Print Me.Recordset!MyField
Debug.Print Me.Recordset.Fields("MyField")
And if you have a control with that same name as Access usually assigns names like that to textboxes, then follow June7 advice and avoid having controls and fields with the same name. Also what welshgasman said is true, open an unbound form and you will not have any field associated. Bind it to a table and it will have the fields of that table associated to the form. Bind a query and you will only have the fields of the query associated to it.
Code:
Debug.Print Me.Recordset!MyField
Debug.Print Me.Recordset.Fields("MyField")
Debug.Print Me!MyField
Debug.Print Me.Controls("MyField")
Will return the same, but are not the same thing. June7 suspicion is true, Me looks for controls first, then it looks for items in the recordset if no controls were found with that name.
And I will add that the bang (!) works when there are Item properties inside the property. Which is why this:
Code:
Debug.Print theForm.Controls.Item("txtSomething").Value
Can not be simplified like this:
Code:
Debug.Print theForm.Controls.Item!txtSomething.Value
TL;DR
Watch your variables and get your references from there. Don't fall into the Me trap, don't wait for MS ACCESS to know what you mean by Me, specify which collection you're trying to get references from. If it's controls, then Me.Controls, if it's recordset, then Me.Recordset.