Results 1 to 6 of 6
  1. #1
    sushi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    9

    Linking SubForms

    I'm attempting to build a database to streamline paperwork in the coming year.



    Items are purchased together on a Product Order. Each Product Order can have multiple Lots with a Lot Price and each lot can have multiple products. (The price of the products is split at our own discretion from the price of the lot; this product amount is more important than the lot amount but the sum of multiple products in a lot must equal the amount of the lot with rounding rules). All lots purchased are new as well as all products (products and lots are unique and cannot be re-ordered). Each purchase order can have one lot or many hundreds of lots; each lot may be one product or hundreds of products.

    Our structure is:
    tbProductOrder (Main Table)
    tbOrder Details (tbProductOrderFK)
    tbLot Details (tbOrderDetailFK)
    tbProduct Details (tbLotDetailFK)

    I figured out how to put together a Product Order with Order Details but am getting lost on how to add Lot Details to the Order Details with relationships like nesting dolls.
    The goal is to have a PurchaseOrder Form with a Tab for inputting the PurchaseOrder Details, with two stacked sub-forms in datasheet view (one to input the Purchase Details & one to input the related Lot Details), and then a final Tab to input the financials and display the associated financial queries.

    My guess as how this is accomplished are two subforms (Purchase Details & LotDetails) with master/child linking the Purchase Detail ID’s. Then, these stacked on a third subform where if the purchasedetailID is pressed the associated LotDetails open below (the master of this form linked to ProductOrder of the main form). This could be way off (maybe this is why it’s not working).

    Attached is a test database to illustrate what’s being attempted. PO Streamlined.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    tbProduct would link to OrderDetail.Product
    tbLot would link to OrderDetail.Product.Lot

    subform in a subform
    you may want OrderForm with sub:OrderDetailSubform with sub:ProductDetail


  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.

    Forms can be nested 7 deep. However, only the last level can be in Datasheet or Continuous view.

    Review 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.

  4. #4
    sushi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    9

    Success (Finally)

    JUNE7: Thank you for the link to FMS "Synchronizing Two Related SubForms"

    Sometimes a single word, in this case "Synchronizing", yields a better search result in google.

    After reading through your page, trying out their zipped example, still being completely jumbled then researching in google this page appeared:

    http://www.lunasystems.com/tips.htm

    It provided the missing link; the two subforms are now synchronized with each other and the primary form. Yay!


    And re RANMAN257:

    There are no spaces in the actual database only field typos in forum posts. *facepalm* Since working with access spaces have started disappearing everywhere. IThoughtMyGrammerWasBadPreviously!

    Now off to the next snafu...

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by June7 View Post
    However, only the last level can be in Datasheet or Continuous view.
    The truth but not the full truth

    1. You have a unbound form with continuous subform on it., and an unbound text box. Subform's OnCurrent event writes it's primary key value into unbound text box. You can add a second continuous subform, and link it to unbound control. As result, whenever you select a record in 1st subform, matching entries are displayed in second one. Both forms behave exactly as form-subform pair.

    2. You can go even further. Instead of second continuous subform, you insert another unbound form, move unbound text box there, and insert the second continuous subform also there. And now you can add a 3rd continuous subform onto 2nd unbound form, which will be synchronized with 2nd continuous subform.

    Of-course the need for such design is questionable, but it will work anyway.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Think this is extending the concept provided in the referenced FMS tutorial.

    And think the restriction for truly nested subforms still holds.
    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.

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

Similar Threads

  1. Linking forms without using subforms
    By tomnsd in forum Forms
    Replies: 3
    Last Post: 01-22-2013, 12:41 PM
  2. Help with append queries and linking subforms
    By MelonFuel in forum Queries
    Replies: 1
    Last Post: 07-11-2012, 01:47 PM
  3. Linking Subforms on Unbound Master Form?
    By 10 Gauge in forum Forms
    Replies: 8
    Last Post: 07-21-2011, 08:06 AM
  4. Replies: 4
    Last Post: 06-22-2011, 10:53 AM
  5. Problem linking subforms, filtering and uploading
    By sameerk0286 in forum Access
    Replies: 1
    Last Post: 12-09-2010, 09:18 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