none
Navigate subform A from subform B RRS feed

  • Question

  • You all ROCK!  Thank you for all the support you have provided over the years.  So here is my newest issue:

    Main From = MF

    Subform A = SFA

    Subform B = SFB

    Both have a key field MDU_ID:  [SFA].[MDU_ID] and [SFB].[MDU_ID]

    What I need to happen is:  When a record on Subform B (continious form) is selected, that reecord is displayed on SFA (single form).  Both forms are contained in the same main form (MF).




    • Edited by Mark Matzke Wednesday, December 20, 2017 7:21 PM Typo
    Wednesday, December 20, 2017 7:19 PM

Answers

  • Hi Mark,

    You can set this up without using code. For example, how are the two subforms linked to the main form?

    I am thinking only one of them is, and you're basically trying to link the other one to the first subform.

    If so, one technique is to place a hidden textbox on the main form containing the linking field from the first subform, and you can then link the second subform to the hidden textbox.

    Just my 2 cents....

    • Marked as answer by Mark Matzke Thursday, December 21, 2017 2:03 PM
    Wednesday, December 20, 2017 8:42 PM

All replies

  • What I need to happen is:  When a record on Subform B (continious form) is selected, that reecord is displayed on SFA (single form).  Both forms are contained in the same main form (MF).

    Hi Mark,

    In words, when you enter a record in SFB, you can grab the record_ID of that record.

    Then modify the RecordSource of SFA using:

        SFA.Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = "  & grabbed record_ID.

    Imb.

    Wednesday, December 20, 2017 7:37 PM
  • Right on, but to simplify it for testing, I am doing on click.  The problem is that I do not understand the basis of your code.  I tried it verbatim, then with a few modifications, but it errors out.  Any other suggestions?

    Private Sub Project_Name_Click()
    'Forms![MF]![SFA].Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = " & record_ID
    'SFA.Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = " & record_ID
    'Forms![MF]![SFA].Form.RecordSource = "SELECT * FROM MyTable WHERE MDU_id = " & MDU_ID
    SFA.Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = "  & grabbed record_ID
    End Sub
    Amending reply:  I should have said, none of these worked, not even our exact code.



    • Edited by Mark Matzke Wednesday, December 20, 2017 8:18 PM Amending reply
    Wednesday, December 20, 2017 8:16 PM
  • Hi Mark,

    You can set this up without using code. For example, how are the two subforms linked to the main form?

    I am thinking only one of them is, and you're basically trying to link the other one to the first subform.

    If so, one technique is to place a hidden textbox on the main form containing the linking field from the first subform, and you can then link the second subform to the hidden textbox.

    Just my 2 cents....

    • Marked as answer by Mark Matzke Thursday, December 21, 2017 2:03 PM
    Wednesday, December 20, 2017 8:42 PM
  • Right on, but to simplify it for testing, I am doing on click.  The problem is that I do not understand the basis of your code.  I tried it verbatim, then with a few modifications, but it errors out.  Any other suggestions?

    Private Sub Project_Name_Click()
    'Forms![MF]![SFA].Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = " & record_ID
    'SFA.Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = " & record_ID
    'Forms![MF]![SFA].Form.RecordSource = "SELECT * FROM MyTable WHERE MDU_id = " & MDU_ID
    SFA.Form.RecordSource = "SELECT * FROM MyTable WHERE Record_id = "  & grabbed record_ID
    End Sub
    Amending reply:  I should have said, none of these worked, not even our exact code.

    Hi Mark,

    I am not surprised that things did not work. I used descriptive language, and that is not understood in your environment.

    - Did you write the record_ID from SFB to the variable record_ID; did you declare that variable?

    - SFA is the nomenclature you used in your example. You have to substitute that for the real name of the subform control.

    - I used MyTable, you have to use the real name of the table where the record is from.

    - What is the real name of the ID-field in that table.

    Perhaps I had better written down:

        <SFA>.Form.RecordSource = "SELECT * FROM <MyTable> WHERE <Record_id> = "  & <grabbed record_ID>

    where all between <> have to be substituted with real values.

    Imb.

     

    Wednesday, December 20, 2017 8:46 PM
  • Sorry, but I'm lost here.  No, I have not made any declarations due to my own inexperience.  I did research Me.Recordset.FindFirst "[MDU_ID] = "'" & 333 & "'"" just to move around on one form, and i couldn't figure that out.  I also tried Forms!MF.Form.SFA.Form.City = "Test" on SFB, and that did work, but on the current record displayed on SFA.  Sorry for being an idiot, but I need someone to make this simple enough for me to understand.
    Thursday, December 21, 2017 3:52 AM
  • Hi Mark Matzke,

    you can try to refer link below may help you.

    Synchronize Two Related Subforms on a Microsoft Access Form

    if then also the problem is persist then I suggest you to post the sample database.

    so that we can check it and see what exactly you are doing in it and what needs to be changed to solve the issue.

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 21, 2017 8:26 AM
    Moderator
  • Sorry, but I'm lost here.

    Hi Mark,

    I am sorry, that was not a direct help for you.

    I have concentrated myself on working with dynamical sql-strings and dynamical forms. This has given me far more possibilities then standard Access offers, but also that I have not too experience in the standard Access way.

    On the other hand, I would be glad to help you, but many other specialists are also available on this forum.

    Imb.

    Thursday, December 21, 2017 11:55 AM
  • I ended up using TheDGuy's suggestion.  It is not how I wanted to write this, but I need it done, and it was simple enough to write.  Thank you everyone, have a Happy Hanukkah, Merry Christmas, and Happy New Year!
    Thursday, December 21, 2017 2:07 PM
  • Hi Mark,

    Glad to hear you got something to work in the mean time. When you're ready to move on to a better solution, just let us know.

    Merry Christmas!!!

    Thursday, December 21, 2017 4:20 PM