Results 1 to 15 of 15
  1. #1
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161

    Using M!MyField> or Me.Recordset!MyField

    This is something I had not considered before, but I have a form with a source specified for the recordset.
    I have mostly been in the habit of accessing data from the recordset (current record) using the syntax Me.Recordset!MyField
    MyField is only in the recordset and is not a field on the form itself, otherwise I'd be using Me.MyField
    However, I now discover that I can also just use the syntax Me!MyField


    Both work, but neither uses intellisense unfortunately.

    My question is, are these two syntax's equivalent or is there a subtle difference? What would be the best practice?

    (edit - should have been Me!MyField in the post title)
    Last edited by Miles R; 03-12-2023 at 11:06 AM. Reason: Additional info

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have never used Me.Recordset!MyField syntax.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Bang operator (!) does not invoke intellisense so there's that. Without getting too complicated, it also makes your reference late bound, causing the reference to refer to the default member. If you use Option Explicit (and you should) then you can prove it thus: change Me!myField to Me!myFeeled and it should compile but will fail at run time. Then change to Me.myFeeled and try to compile. However the bang operator seems required when dealing with most aspects of recordsets.

    I'm curious as to how you can refer to a form recordset field by referencing the default member of a form, which I always took to be its controls and not fields in its underlying recordset.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I normally give controls names different from fields, such as tbxQty for field Quantity. Then in VBA I reference field with Me!Quantity and textbox Me.tbxQty.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I normally give controls names different from fields, such as tbxQty for field Quantity. Then in VBA I reference field with Me!Quantity and textbox Me.tbxQty.

    Maybe with ! Access looks for a control first and when not found looks for field. Maybe it would be more efficient to use Recordset qualifier.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    There is a big difference between using a dot or bang(.,!)

    Intellisense can only be used for early bound objects which is what a dot does. A bang late binds so intellisense cannot work

    objects such as recordsets are late bound by default- vba cannot know the structure until the code is running so elements such as fields have to be late bound whilst the general properties and actions are known because they form part of what the object actually is.

    Early binding is preferred wherever possible since that will generate an error at compile time (for a typo for example) whereas late binding would not detect the error until runtime

  7. #7
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161
    Thanks to everyone that has replied.
    Firstly, June says she (or is June the month?) has never used the Me.Recordset!MyField syntax and always uses Me!MyField syntax. Micron seems to not use the Me!MyField syntax.
    Yes, I always use
    Option Explicit and am aware the code will compile with a wrong field name but fails at runtime. Have fallen fowl of this many times when misspelling field names.
    Also, like June, I do use different names for fields that are objects on the form than fields in the recordset.
    Maybe June is correct in the supposition that with ! access looks for a control first, then for a match in the recordset. In which case, it would probably be most efficient to use the
    Me.Recordset!MyField syntax.

    I hope someone looking at this thread knows the definitive answer to this and how Access actually works in this regard.
    In the meantime, I will continue to use the
    Me.Recordset!MyField for fields in the recordset and Me.FieldName for objects on the form.

    Edit
    Just to add a bit of confusion to the matter, I just tried an experiment. I referred to the field in the recordset using Me.MyField. Intellisense found MyField and it complied, despite MyField only being in the recordset and not a control on the form. Don't know what implication this has for the comments about being early or late bound. Clearly intellisense in this case does know about the fields in the recordset.
    Edit

    (incidentally, on my previous thread https://www.accessforums.net/showthread.php?t=87712, I got problems with Me.Recordset!MyField that went away when using Me!MyField, so there must be some subtle difference between the two).

    Last edited by Miles R; 03-12-2023 at 05:03 PM. Reason: Additional info

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161
    Thanks Moke. I've also seen a good video on the use of bang or dot. - Should I use the Bang! or Dot. Operator in Microsoft Access Forms - YouTube
    Both also suggest the use of Me. is not needed at all. (it helps with intellisense though)
    Still does not explain why Me!MyField is just as good as Me.Recordset!MyField in accessing MyField from the Recordset. Maybe Recordset is the default member for the form?
    I'll probably change my code to use Recordset!MyField for recordset items and just the FieldName for items on the form without the Me.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Miles R View Post
    Thanks Moke. I've also seen a good video on the use of bang or dot. - Should I use the Bang! or Dot. Operator in Microsoft Access Forms - YouTube
    Both also suggest the use of Me. is not needed at all. (it helps with intellisense though)
    Still does not explain why Me!MyField is just as good as Me.Recordset!MyField in accessing MyField from the Recordset. Maybe Recordset is the default member for the form?
    I'll probably change my code to use Recordset!MyField for recordset items and just the FieldName for items on the form without the Me.
    I seem to recall from a mention by Pat Hartmann on AWF that Access supplies all the fields of the source for a form, even if there are not matching controls on the form. This applies to a table source?
    That way I can set AmendedDate and AmendedBy in the Before_Update event of the form, when controls for thos efields do not exist.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe June is correct in the supposition that with ! access looks for a control first, then for a match in the recordset.
    I guess by now you know that is not the case based on the link in post 8. I could have recited (ok, written) the explanation but as I mentioned before, I was trying to avoid the complexity since you asked what the difference and best practice was. I think it's fair to say that not everyone agrees on everything or adopts the same practices as being "best". I for one don't use ! unless it involves a recordset simply because I believe in vetting my code at compile time as much as possible. What I have gathered from this is that for some strange reason, you are able to refer to a form recordset field using ! seeing as how it's supposed to pass a name to the default property to whatever precedes it. Everything I've ever read tells me that for a form, the default is its controls collection, not its underlying fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    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.
    Last edited by Edgar; 03-13-2023 at 01:54 AM. Reason: added and corrected behaviors

  13. #13
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161
    Wow,

    There's a lot to read and inwardly digest there.
    I think Micron is right and not everyone agree on the best practice. Also I think intellisense may be took intelligent and is covering up some poor programming practice in some cases.
    I'll probably stick with my new standard of using Recordset!MyField for recordset items and just the FieldName for items on the form.
    Don't see the need to use Me. at all now - it just clutters up the code. Possibly people might use it for intellisense reasons.

    No matter what you think you know, there's always something you don't!

  14. #14
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I always use Me. for form controls as I like the intellisense and I know exactly what I'm referring to, I avoid the Me! unless I have to use it.
    Just a personal preference .
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    Quote Originally Posted by Micron View Post
    I guess by now you know that is not the case based on the link in post 8.
    Hi, Micron
    You can test it yourself.
    1. Create a blank form
    2. Bind it to some table, let's suppose it's a people table and PersonID is its primary key (just for the test)
    3. Add an unbound textbox and assign a name different from any field in the table like txtMyTextbox (just for the test)
    4. Put some code inside the Form's module, how about the Form_Open event? (just for the test)
    5. Type: Me.PersonID.
    6. What do you get from intellisense? I got a list with just Value, and that's it. To me, that means it's taking it from the Recordset.
    7. Now change the name of the textbox to one of the fields in the table. how about PersonID?
    8. Type: Me.PersonID.
    9. What do you get from intellisense? I got the list that I'd get from a textbox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-20-2021, 07:31 PM
  2. Replies: 6
    Last Post: 04-22-2018, 09:12 PM
  3. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  4. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums