Results 1 to 4 of 4
  1. #1
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30

    Trying to get value of subform field via variable

    This works:


    Debug.Print subFormName!FieldName.Value

    But this doesn't:
    varFieldName = "FieldName"
    Debug.Print subFormName.Fields(varFieldName).Value

    It gives me the following error:
    "Compile error: Method or data member not found" with the ".Fields" highlighted.

    Why?

  2. #2
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Well I just fixed it by changing it to:
    Debug.Print subFormName(varFieldName).Value

    Not sure why that worked, so if someone could explain, that would be great, but it does appear to be working. I'm still a novice when it comes to this syntax.

    Thanks!

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe obvious, but the 2 styles of references do not evaluate the same way.
    ! will provide late bound reference to the name that follows it and pass it to what comes before it. Thus fieldName (no quotes) can be evaluated.

    . (as in subFormName.FieldName) is explicit and will evaluate upon code compile whereas ! will not. Thus misspell control name with ! and it will not blow up until that code line executes.

    Now for the other method (at least I'm fairly certain of this next part)
    It requires a delimited name value be passed to the Controls collection (a reference to that collection is invoked by Controls), which you are not delimiting unless you have
    subFormName.Fields( & "'" & varFieldName & "'")

    I made single quotes red and larger so as to be seen as surrounding varFieldName with ' You can also use doubles but it's a bit funkier to do so.

    Using subFormName(varFieldName) you avoid using the Controls collection. However, your variable name suggests it is a variant, which will be a problem if any situation returns any other data type other than text. I cannot recall seeing the actual version that you say works, so I cannot comment on either its efficiency or safety.
    BTW - Value isn't needed for what you show as it's the default property of a text or combo box. An exception could be where lookup fields are being used, but you don't use those, right?

    Hope some of that helps.
    Last edited by Micron; 06-26-2019 at 03:05 PM. Reason: clarification & added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Thanks for the info. I'm going to study up on this.

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

Similar Threads

  1. Referencing a Subform in a Variable
    By Athar Khan in forum Programming
    Replies: 6
    Last Post: 09-27-2017, 01:31 AM
  2. Replies: 6
    Last Post: 11-20-2014, 03:37 PM
  3. Replies: 2
    Last Post: 01-18-2014, 11:51 AM
  4. Replies: 3
    Last Post: 08-22-2012, 09:01 AM
  5. Set date field on subform to date variable
    By laavista in forum Access
    Replies: 4
    Last Post: 06-30-2010, 06:32 PM

Tags for this Thread

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