Results 1 to 13 of 13
  1. #1
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18

    Main form with two related subforms solution not working.

    Hi,
    I'm using Access 2003 and I have a problem getting two related subforms to work properly on a main form. This same problem has been posted here before, and I already knew the solution that was posted, but the solution isn't working for some reason. I don't know why.




    I have three data tables and three bound data entry forms. These are tblClusters, tblSystems, tblPlanets, frm Clusters, sfrmSystems, and sfrmPlanets. Since Clusters can have many Systems, and Systems can have many Planets, the table relationships are one to many, with Referential Integrity enforced. I'm using frmClusters as the main form, sfrmSystems as the first subform, and sfrmPlanets is the second subform. On the main form, the name of the sfrmSystems control is SystemData, and the name of the sfrmPlanets control is PlanetData. The main frmClusters does not have a name property, neither on a bound nor an unbound form.

    The Problem: "A Subform Cannot Link to Fields in a Subform of its Parent"

    The Published Solution (substituting the names of my own tables and forms) is as follows:
    1. Create an intermediate text box on the main form (frmClusters) to act as the LinkMaster value.
    2. Set the text box's Control Source property to the idxSystemID field in the first subform (SystemData).
    3. Name the text box control 'txtSystemID'
    4. Use this text box in the LinkMaster field of the second subform control properties.

    Everything is fine up to where it's time to use the text box as the LinkMaster field in the second subform (PlanetData). The text box (txtSystemID) is not shown in the drop down list of available LinkMaster fields in the second subforms property box. Only the fields in the main form's underlying table (tblClusters) are shown. If I try to type in the name of the text box control manually, Access returns a message saying that the field does not exist and cancels the attempt to create the link.

    I have tried this solution using both an unbound main form and a bound main form. The results are little different. Using an unbound Main Form, Access returns the message that it "Can't build a link between unbound forms". So either way, this solution isn't working for some reason, yet other people who have asked the same question found that the solution did work for them. The only logical conclusion is that I've done something wrong, but I've checked everything several times and I can't find anything wrong. Is it a bug in Access 2003? Any advice would be hugely appreciated.

    Thanks in advance for any replies.

  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 am using this technique and it works.

    What is name of the subform container control? The textbox expression must reference it as prefix to the field name. Example from my db: =[ctrHoles]![TestHoleRecID]

    I always name container different from the object it holds. In the example it is ctrHoles.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Quote Originally Posted by June7 View Post
    I am using this technique and it works.

    What is name of the subform container control? The textbox expression must reference it as prefix to the field name. Example from my db: =[ctrHoles]![TestHoleRecID]

    I always name container different from the object it holds. In the example it is ctrHoles.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    As I said in second paragraph above: "
    On the main form, the name of the sfrmSystems control is SystemData, and the name of the sfrmPlanets control is PlanetData."

    I have done the referencing properly (as far as I know) because the added text box is working properly. The command the text box uses to get the idxSystemID data from the first subform is "=SystemData.Form!idxSystemID". The name of the text box is txtSystemID.

    I do not know how to upload, but this should be simple and straightforward. Something is preventing the text box control from appearing as a LinkMaster field. I just need to know what can prevent that.

  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
    Since I can't replicate the issue, hard to advise a solution. The setup and syntax I describe works in my db.

    Instructions for uploading files at bottom of my post.
    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I recently helped someone with a similar problem. There was a main form a sub form and a sub sub form. But they wanted both sub forms on the main form.

    Quote Originally Posted by Santonin View Post
    I have three data tables and three bound data entry forms. These are tblClusters, tblSystems, tblPlanets, frm Clusters, sfrmSystems, and sfrmPlanets. Since Clusters can have many Systems, and Systems can have many Planets, the table relationships are one to many, with Referential Integrity enforced. I'm using frmClusters as the main form, sfrmSystems as the first subform, and sfrmPlanets is the second subform. On the main form, the name of the sfrmSystems control is SystemData, and the name of the sfrmPlanets control is PlanetData.
    If I understand correctly, you have tables

    Tables

    tblClusters------------------> tblSystems------------------> tblPlanetsandand
    and
    Forms
    frmClusters (Main form)-----> sfrmSystems (sub form) ------> sfrmPlanets (sub-sub-form)



    In the attached dB, the design is

    Tables
    tblWorkDays------------------> tblTaskActivities-----------------> jnctTasksExceptionsand
    and
    Forms
    frmMain (Main form)-----------> sfActivities(sub form) -----------> sfExceptions(sub-sub-form)


    The record sources of the forms are queries. The linking PK/FK fields are on the forms, but not visible.

    Look at the Master/Child linking fields. The linking fields between the frmMain and the first subform are normal.
    The Master/Child linking fields between the first sub form, "sfActivities", and the second sub form (sub-sub-form) "sfExceptions" are different.

    The master link is a fully qualified link to a control on "sfActivities". The link is "forms!frmmain.DailyActivities.form!taTaskActiviti esID_PK".
    The child link is just a normal control for the FK field in "sfExceptions".

    In the form current event of the sub form "sfActivities" is a line that re-queries the sub-sub-form "sfExceptions".



    To see this in action, in the main form, select associate "Banta, Ron" (green header).
    In the sub form "Daily Activities" (blue header), clicking on any of the 5 records changes the sub-sub-form "sfExceptions" records (peach header).
    Attached Files Attached Files

  6. #6
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    The description you've given me of the DB you sent describes my scenario more or less exactly. And I can see you've spent a lot of time setting up your reply, so let me do you the honor of digging through your DB and do my best to see if I can find the answer I'm looking for there. For now, thank you very much for your time and possible solution. I'll come back here once I'm done and either send a hearty thanks and mark the thread as solved if I find my answer in your DB, or post an update if I can't find the answer. That should be by the end of the day today if I don't get distracted, or else early tomorrow at the latest.

    Regards,
    Doug

    Update:
    I've had a chance to have a quick look at your DB. I started by going to the properties for the second subform to look at the link fields. Despite the number of times I tried to enter a reference like yours, I guess I just never got the right combination of Forms, Form, periods, and exclamation marks. When I used your syntax, Access accepted the references without telling me the field didn't exist. So thank you for that. But the foreign key field (lngSystemID) in the Planets subform still isn't updating when the primary key field (idxSystemID) changes in the first subform. I haven't had time yet to look deeper because of a mystery I found in your DB.

    The first thing I want is to see the link field update in your second subform when the record changes in the first subform. You said the link field was invisible, and indeed it is when the form is open. Normally that wouldn't be a mystery for me, except that the field's visible property is set to Yes and I haven't found any code yet that sets that property to No. Once I solve that little mystery and make the field visible when the form is open, then I can see it change when I change records in the first subform. Once I see that, then I'll see if I can find out where I've got something wrong in my DB.

    All of this is going to take me some time. As I said, I'm fairly new to Access, and I'm trying to learn out of two books (
    Microsoft Access 2003 Bible and Microsoft Access 2003 Programming by Example (which I just bought a few days ago).
    Now I have your DB to learn from as well (so long as my canceled Office 365 account remains active). One thing your DB has already confirmed is something that I already knew, which is that I don't really know anything about Access yet, despite the amount of time I've spent trying to learn it.

    I know threads are supposed to be marked as solved, so I'll try to find the solution to my problem in your DB asap, or else post my DB (thanks June7) and let someone else show me where I'm going wrong.
    Last edited by Santonin; 12-16-2018 at 08:31 PM.

  7. #7
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Update 2:

    I think I've now done all I can to get this issue sorted, and I'm starting to think this is an issue with Access 2003.

    To keep this short, it appears that the only thing needed to make the link between the two subforms work are the LinkMaster and the LinkChild fileds. Your LinkMaster field in your Exceptions subform references the Primary Key field in your Activites subform, which I thought wasn't possible, so now I don't understand why a reference can be typed in manually when it isn't available via the dropdown field list, but that's fine. I got it, and now I've set mine the same way. Access accepts both the LinkMaster and LinkChild references, so my second subform is now linked directly to the first in exactly the same way your Exceptions subform is linked to your Activities subform.

    I also observe that your Exceptions subform has no VBA events tied to it. So, unless I am mistaken, the link is working purely off the LinkMaster and LinkChild fields. My second subform also has no events tied to it yet. So I assume that code is not necessary to make the link work just as yours does.

    I looked at the relationships and the join types and made two changes to conform to your join types (I still don't really understand Join types. What I think is the right join type never is it seems).

    I looked carefully at the names of your subform controls, and mine are set up the same, with a different name for the name of the first subform control (I noted that your second subform control has the same name as the form itself). So where you name your sfActivites form as DailyActivities, I named my sfrmSystems as SystemData. In the reference itself, I copied your syntax exactly as follows:

    Link Master Fields: Forms!frmClusters.SystemData.form!idxSystemID

    Link Child Fields: lngSystemID (the foreign key field in the Planets table)

    Further to all of this, when it all failed, I switched the Link Master field over to the intermediate text box on the main form which reads the System ID field from the first subform. It made no difference.

    As far as I can tell, everything is done properly, and yet the foreign key field in the second subform still will not update when the primary key changes in the first subform. Therefore something is still wrong somewhere. At this point, I feel like I've done my best to isolate this issue, and as a result of my failure, I want to upload my DB for someone to look at if they have the time, however the upload manager won't let me upload a .7z file nor will it let me upload my .mdb file. The upload keeps failing, which is why I never bothered to learn how to upload. It should be simple, but it never is.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use Windows Compression to create ZIP file. Interesting you cannot load .7z since I have seen them, maybe forum changed to be more restrictive. Regardless, I cannot extract them.

    Import only the relevant objects to another db, delete most data, see if that is small enough to load without zipping.

    In any case, be sure to run Compact & Repair first.
    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.

  9. #9
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Wow. That was really easy. I didn't know about Windows Compression before (new to Win10). Ok let's see if I can upload the .ZIP file...

    Success! It let me. Thanks for that. The .mdb was over 2Mb so maybe that's why the board failed the upload. This zip file is under 50k. That's a lot of overhead.

    Good luck finding the problem. I just hope that whatever it is doesn't make me look stupid.

    Also, I loaded up my 2003 .mdb into Access 365 and converted it to the 2007 format in the hopes that might fix the problem. It didn't.
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, I am getting the described issue.

    I have tried this technique only twice and it's been a while. Now remember having difficulty with one db and eventually abandoned this approach. Just took another look at the other and realized the subforms have tables as SourceObject, not forms. This db works.

    The only way I could get your db to work is to add code to sfrmSystems Current event: Me.Parent.sfrmPlanets.Requery

    Appears you tried Requery code in txtSystemID Change event. This event only triggers with typed input, not a calculation.

    Code to set visibility of chrUnlock and its associated label can be simplified one-liner (Load event not necessary): Me.chrUnlock.Visible = Not Me.blnLocked.
    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.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Santonin View Post
    Update 2:

    I also observe that your Exceptions subform has no VBA events tied to it. So, unless I am mistaken, the link is working purely off the LinkMaster and LinkChild fields. My second subform also has no events tied to it yet. So I assume that code is not necessary to make the link work just as yours does.

    In my Post #5, I did say
    In the form current event of the sub form "sfActivities" is a line that re-queries the sub-sub-form "sfExceptions".

    Forms
    frmMain (Main form)-----------> sfActivities(sub form)-----------> sfExceptions(sub-sub-form)
    .........................................(Current event code here)


    PK (and FK) fields are not visible to the user because I use autonumber PK fields to link tables.
    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key. Also see Microsoft Access Tables: Primary Key Tips and Techniques






    -----------------------------------------------------------------------------------------------------------------------------------
    EDIT:

    I've been looking at your dB - I added the line
    Code:
        Me.Parent!sfrmPlanets.Requery
    in the Form Current event for the form "sfmPlanets" and seems to work without problems.


    You should have the lines
    Code:
    Option Compare Database
    Option Explicit
    at the top of EVERY code module.


    You should not have multiple commands on one line. The ":" (colon) is still supported but is not recommended to use due to readability arguments
    You have
    Code:
    Private Sub blnLocked_AfterUpdate()
        If Me.blnLocked = True Then
            Me.chrUnlock.Visible = True: Me.chrUnlock_Label.Visible = True
        Else
            Me.chrUnlock.Visible = False: Me.chrUnlock_Label.Visible = False
        End If
    End Sub

    better is
    Code:
            If Me!blnLocked = True Then
                Me.chrUnlock.Visible = True
                Me.chrUnlock_Label.Visible = True
            Else
                Me.chrUnlock.Visible = False
                Me.chrUnlock_Label.Visible = False
            End If
    Best is
    Code:
    Private Sub blnLocked_AfterUpdate()
        Me.chrUnlock.Visible = Me!blnLocked
        Me.chrUnlock_Label.Visible = Me!blnLocked
    End Sub

  12. #12
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Wow! Problem solved! You're right Ssanfu. I did READ that line about the form requery - and then I forgot about it when I redid the link fields (/slaps forehead). And June7 is right, I DID try a form requery before I contacted this board, but I didn't know that the Change event only triggers on an edit change, not a calculation change (/sigh). Thank you June7 for spotting and clarifying that one. Your tips on VBA programming are over my head at the moment, but that's why I bought my new VBA book. I know that my feeble attempts at coding aren't even up to Novice standard yet. I don't even know what a "module" actually is yet, but I'll learn. In the meantime, I'll copy your list of tips and implement them right away. Formal VBA study is going to be next, right after I finish this first book on database design (Access 2003 Bible). The problem with books is that they can't talk, so learning is slower. In my case, it's a lot slower.

    I've learned important lessons here and I can't thank you people enough. I wish I had found this solution on the internet instead of the half-baked one that I did find. In my novice opinion,
    a subform CAN be directly linked to another subform on a parent form as long as you requery the subform properly as described. I didn't find anything through DuckDuckGo that said that, nor did I find anything that showed me the procedure I learned here. Thank you SO much for taking the time to look at this for me. And thank you Ssanfu for your DB. It has some great procedural study material in it. I will make the most of it while my Office 365 account is still active.

    Lastly, thank you for being here for people like me. You people are champions.

    Now let's see...how do I mark this problem as solved. I don't see an option here, so I'll look for one after I post this closing reply. Take care folks. Have a good Christmas and best wishes forever.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad it is now working.

    It was just about 6 months ago I found a dB that linked sub forms like the demo (subsubform.zip), so we all are learning (I hope).


    Good luck with your project.......

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

Similar Threads

  1. SubForms in access main form
    By TskcAccessdev in forum Forms
    Replies: 17
    Last Post: 10-17-2017, 11:28 AM
  2. can't put 2 subforms on a main form
    By cjohnson in forum Forms
    Replies: 6
    Last Post: 04-28-2017, 01:19 PM
  3. Replies: 11
    Last Post: 05-13-2015, 11:10 PM
  4. Multiple subforms to one main form
    By mick3911 in forum Forms
    Replies: 8
    Last Post: 02-25-2013, 09:40 PM
  5. Replies: 0
    Last Post: 12-16-2008, 07:49 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