Results 1 to 6 of 6
  1. #1
    Sa'El is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    40

    Passing A Value from an Opened Form to Another Form that Will be Opened

    Hey All,



    If I would have my way I'd set-up a form like this (sub bullets would be subforms):


    • Main Form (A)
      • Single Form Subform (B)
        • Single Form Subform (C)

      • Datasheet Subform(s)


    The datasheet subforms are inconsequential as the real problem is that Access doesn't allow the 3rd layer of subform as a single subform (characterized by the square bullet above). So to "resolve" the problem, I am separating Main Form A and so Form B no longer has a Parent-Child relationship with Form A, by the Case ID field.

    By design, when Form B opens Main Form A is still open, so I set the Case ID field in Form B to look up the value that's currently in Main Form A to pass the value. But unfortunately the value isn't being passed.

    There is essentially 2 things I want

    • While form A is opened, when form B is opened I want the record(s) in Form B to be the same record that's currently in Form A via the Case ID field.
    • If Form B doesn't have any corresponding records to Form A (as in there is no matching Case ID fields in both forms), I want the Case ID in Form A to pass its value to the Case ID field in Form B
      • If I go to a new record in Form B, I do want the same Case ID field to be passed from form A


    I hope this makes sense. I will clarify anything that doesn't seem clear. Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't see a square bullet.

    Why can't the 3rd level form be Single View? Works for me.

    Not sure I understand what you are doing but maybe this will be of interest http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp
    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
    Sa'El is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    40
    Thanks for the quick reply as always, June.

    The square bullet was subform C (it looks square when you're typing up a post).

    Anyway, when I did a 3rd level subform view, it wouldn't navigate past the 1st record. The next record button was grayed out. I spent a lot of time troubleshooting it, and then ultimately got an error in the link master fields when I tried to set it.

    Having linked master fields in the past, I don't think I did anything wrong, but then again, I've not had to go to a 3rd level before in past projects, so are there unique aspects when venturing to a 3rd subform level? As you got to lower levels, are you suppose to link the just the direct parent fields, or all parent fields of the subform? To be honest, this isn't as important to answer as I actually prefer the design I have in separating the forms. So if you have limited bandwidth, I'd prefer you answer the paragraph below.


    When I checked the link out, while it has great info, the second half of the page talks about an unbound control. My main issue is that I want the Case ID in Form B, which would be a Child of Form A, to have the same value as the Case ID in Form A automatically, without the end user having to type it in. If I am simply inputting a syntax in the control source that references the Case ID in Form A, then how will the Case ID value into Form B be entered? I am not just referencing already existing records in Form B, I also need to create new records linking the correct Case ID from Form A.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The 3rd level form should be navigable if set up properly.

    The unbound textbox is referenced in Link properties so that the value will automatically feed to the subform. It's been a long time since I tested this technique but from what I remember, it works.

    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.

  5. #5
    Sa'El is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    40
    Sorry for the delay. Since our posts, I have continued to meet with the customer as I put this together, and while making edits to the design, I realize where my error was: it was a design flaw on my part. If I could explain on a high level: I had a couple of subforms that had distinct functions (well, d'uh ) and in the back-end designed them too distinctly by giving them different parent forms when in reality they should have the same parent form. Thankfully I figured it out.

    Thanks for responding though, June, and that link is a good resource regardless, so there's still value gained. Take care for now.

  6. #6
    jcrist30 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2017
    Location
    Montgomery, AL
    Posts
    1
    You need to set a filter on Form B such that [FormBID]=[Forms]![FormA]![RelatedID], and select "Yes" for "Filter On Load".

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

Similar Threads

  1. Replies: 9
    Last Post: 09-18-2014, 10:16 AM
  2. Replies: 3
    Last Post: 09-14-2012, 04:10 PM
  3. Auto sort a form when opened?
    By newtoAccess in forum Access
    Replies: 2
    Last Post: 03-19-2011, 03:22 PM
  4. Can not close pop up form after second pop was opened
    By snoopy2003 in forum Programming
    Replies: 2
    Last Post: 03-09-2011, 02:56 AM
  5. Check if form is opened by other users
    By ser01 in forum Programming
    Replies: 2
    Last Post: 05-03-2010, 12:07 AM

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