Results 1 to 12 of 12

VBA Syntax for Accessing Record from Subform

  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    43

    VBA Syntax for Accessing Record from Subform

    I am trying and failing to point to a specific textbox.

    I have a main form: Conference Search
    I have a tabbed control where a subform is living: sfrm_Persons_Attending

    I want to be able to edit by clicking a button and opening another form. The subform I'm in will have all the requisite information (Conference_ID_FK) and (Person_ID_FK) to make the requisite relationships tying into the opened form (P2CAdds).

    My VBA looks like this:

    When I click the button, the onclick event is simply:
    DoCmd.OpenForm "P2CAdds"



    When that form loads, however, there is an ON LOAD event that looks like this:
    DoCmd.GoToRecord , "P2CAdds", acNewRec 'This opens a new record; given that it's a new record, I don't have a control source identified yet for the Conference_ID_FK and the Person_ID_FK--I have to pull that from the subform
    Me.Conference_ID_FK.Value = Forms![Conference Search]![sfrm_Persons_Attending]!Form.Conference_ID_FK.Value 'This does not work; I get an error saying:
    "Microsoft Access can't find the field 'sfrm_Persons_Attending' referred to in your expression" --The issue being that "sfrm_Persons_Attending" is not a field--it's a subform!

    Is there something wrong with the syntax? I've looked all over and it seems that this, or variations of where the exclamation point should be, should get me results, but no dice. Am I missing something as to why it's not populating the textbox the way I want?

    Thank you!

  2. #2
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,578
    There is a difference between using the ! and using a dot. One uses the subform control object name and the other uses the actual Name property.

  3. #3
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    43
    Quote Originally Posted by aytee111 View Post
    There is a difference between using the ! and using a dot. One uses the subform control object name and the other uses the actual Name property.
    Would the fact that it's coming from a tabbed form have any impact on this?
    i.e. it's a Main Form (split form) where above the datasheet view is a Tabbed control. On Page 2 of the Tabbed control, I have my subform (sfrm_Persons_Attending). Do I need to take the fact that it's coming from a tabbed control into account when doing my VBA syntax?

  4. #4
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,578
    No, the subform is an object on the main form, not on the tab.

  5. #5
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,578
    Just noticed that you are using a split form, I don't use these and am not sure if this makes a difference.

  6. #6
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    43
    Quote Originally Posted by aytee111 View Post
    Just noticed that you are using a split form, I don't use these and am not sure if this makes a difference.
    I apologize for the lack of images or examples, but it's on my computer at work, which I have no way of getting that information here. But the long and short of it is my main form (Conference Search) is a split form.
    The header has a search in there so that a user may search for the list of conferences. Every conference has to have at least ONE day, but SOME of them have multiple days, which is why I have a relationship table that looks like this (going from memory, so table names and IDs may differ from my actual table at work, but I think you'll get the gist):
    Click image for larger version. 

Name:	Relationships.png 
Views:	10 
Size:	20.4 KB 
ID:	31263
    On the main part of the form, the body, I have my conference information (things identified in the tbl_Conference_Main, per above).
    Underneath that is the tabbed control where page 1 tab is a list of the days of the conference and page 2 tab is a list of the people attending that conference. This is married up by noting the: PersonID, ConferenceID, and ConferenceDayID as identified in the tbl_Person_to_Conference table.

    What I am attempting to do is have the user click a button that will open a form representative of the tbl_Person_to_Conference, whereby it will ingest the PersonID and the ConferenceID when the form loads, taking those values from the subform (within the tabbed control) and placing them in as the values for the PersonID and ConferenceID on the tbl_Persons_to_Conference form.

    It seems that my vba syntax whenever I try to assign those values is messed up because I keep getting an error (from memory--I believe it is 2465) indicating that it can't find the field "sfrm_Persons_Attending", which is the *subform* on page 2 of the tabbed control.

    By my logic (just mine, lol) this should work:
    Me.Conference_ID_FK.Value = Forms![Conference Search]![sfrm_Persons_Attending]!Form.Conference_ID_FK.Value
    I'm telling Access that whatever the value that exists in the Conference_ID_FK field of the persons attending form should be changed for the value of that same field of the subform I just left.

    i.e. if I have Person A going, already, to Day 1 of Conference XYZ, and I want to click a button to relate him to days 2 and 4, it should take the ConferenceID and the PersonID, and throw those numbers in the respective fields. The ConferenceDayID is set as a combo box that populates the available days, using the ConferenceID (see relationships) as a key. That part works great in another form, whenever I'm adding the relationship as new, but it's just this editing thing that's giving me trouble.

  7. #7
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,578
    The "field" it is looking for is the Name property of the subform, as in Forms!subformname!fieldname - that is all you need, none of the other stuff

  8. #8
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,995
    I get the picture that this sfrm_Persons_Attending is the name of your subform. In that case, the syntax is incorrect and Access thinks you're referring to a field. Try
    [Forms]![Main form name]![subform control name].[Form]![control name on subform] where subformcontrol name is the name of the control that contains the subform. It is not the subform itself. One other difference is that you have !Form and not .Form. This is about the best explanation of the use of the operators that I've come across http://bytecomb.com/the-bang-exclama...erator-in-vba/
    Note that you don't need .Value for combos or text boxes as it's the default property. All that being said, it's worth noting that the form you want to pull these values from has to be open at the time. If it cannot be (or even if it can be) sometimes it's easier to pass values to an opening form via the OpenArgs property of the DoCmd.Open method. I also think the statement that the subform 'lives' on the form and not the tab control itself is correct, so I don't see including the tab control in the expression that references the control you want to access.
    Last edited by Micron; 11-13-2017 at 01:27 PM. Reason: spelin
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

  9. #9
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    43
    Quote Originally Posted by aytee111 View Post
    The "field" it is looking for is the Name property of the subform, as in Forms!subformname!fieldname - that is all you need, none of the other stuff
    I'll try that when I get in tomorrow. Thank you!

  10. #10
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    43
    Quote Originally Posted by Micron View Post
    I get the picture that this sfrm_Persons_Attending is the name of your subform. In that case, the syntax is incorrect and Access thinks you're referring to a field. Try
    [Forms]![Main form name]![subform control name].[Form]![control name on subform] where subformcontrol name is the name of the control that contains the subform. It is not the subform itself. One other difference is that you have !Form and not .Form. This is about the best explanation of the use of the operators that I've come across http://bytecomb.com/the-bang-exclama...erator-in-vba/
    Note that you don't need .Value for combos or text boxes as it's the default property. All that being said, it's worth noting that the form you want to pull these values from has to be open at the time. If it cannot be (or even if it can be) sometimes it's easier to pass values to an opening form via the OpenArgs property of the DoCmd.Open method. I also think the statement that the subform 'lives' on the form and not the tab control itself is correct, so I don't see including the tab control in the expression that references the control you want to access.
    Thank you very much for the link! I'll try your suggestion as well when I get in tomorrow and update this post. Thanks again!

  11. #11
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    43
    Just wanted to update:

    It looked like what it wanted was a notification to the name of the tabbed control rather than the name of the subform. It's working now with this:

    Me.Person_ID_FK = [Forms]![Conference Search]![sfrm_Details].[Form]![Person_ID_FK]
    Me.Conference_ID_FK = [Forms]![Conference Search]![sfrm_Details].[Form].[Conference_ID_FK]

    Doesn't look like it needed the actual subform name at all, which is a little weird to me. But it works, which is the result I was looking for at the end of the day. Thank you, all!

  12. #12
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,995
    You're saying [sfrm_Details] is the tab control and you fixed it by referencing it? Doesn't seem right.
    Doesn't look like it needed the actual subform name at all
    You're right you don't and this was pointed out in post 8. It looks like sfrm_Details is the subform control but you seem to be saying it's the tab control.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-30-2016, 02:13 AM
  2. Replies: 1
    Last Post: 02-21-2013, 11:27 AM
  3. Replies: 6
    Last Post: 08-22-2012, 02:24 AM
  4. Replies: 2
    Last Post: 07-25-2012, 04:03 PM
  5. Accessing subform fields
    By nkenney in forum Forms
    Replies: 1
    Last Post: 04-21-2009, 09:10 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
  •  
Tech Forums: Microsoft Office Forums