Subform for Subform

Question

I have 2 tables I would like to create a data entry form for, but I'm not sure of the best way to go about this. I thought I would be able to have a Master Form with a Subform and a button on the Subform that opens another form, but the information entered in the secondary form doesn't save. Is there a better way to go about this that would not require me to get into SQL coding?

The forms I have are:

Container

PO

Item

Container_ID is the primary key for the Container table that links to the PO table.

PO_ID is the primary key for the PO table that links to the Item table.

Item_ID is the primary for the Item table, but that doesn't link anything.

I included the Container_ID on the Item table because I thought it might be necessary. However, I can't use that as a link to the Container table because the items are determined by the PO and there could be multiple PO numbers on a single container. Hopefully that makes sense.

So I had tried to make a Container Master form with a PO subform and a button on the subform that opened an Items form where the items could be entered depending upon the PO. As I said that didn't work out. I also thought about have a linked form from the subform, but I don't know how to make those outside of the Form Wizard.

george_hepworth · Answer

Kwoolridge&nbsp;&nbsp;I want to be sure I understand how this is supposed to work before offering suggestions.&nbsp;Please confirm or clarify.A "container" is what kind of thing? Does a container represent a physical object into which items are placed for shipping, perhaps?&nbsp;How, exactly, does a PO (which I assume means Purchase Order) relate to a container?Do you put items from multiple POs into one container for shipping, for example? Or do you put items from one PO into multiple containers?&nbsp;&nbsp;It ought to be pretty clear how POs and PO Items work, but to be sure. One PO can be used to ship (or sell) multiple Items, which are, in turn, used to identify specific products, prices, etc. for those items. Correct?&nbsp;Or is it the case that there is one container per Item? How do they relate?&nbsp;The actual business rules here will tell us how to approach table design (that has to be correct first) and then how to recommend forms to handle data entry for those tables.Thanks for the clarification.

kwoolridge · Answer

I'm not entirely familiar with the system myself. I know that containers hold physical product and the physical product in those containers can come from various POs (Purchase Orders) which request items. As far as I know, POs are issued for specific items and items are loaded into containers until they're full. So, a container could hold items from a number of POs or a PO could be split between various containers. So, say we have Containers 1 and 2 (CT1 & CT2) and 4 POs (PO1, PO2, PO3, and PO4) with these items.
PO1 - abc123, def456, ghi789
PO2 - zyx098
PO3 - def456, jkl135
PO4 - qrs369, abc123
CT1 might contain abc123 (PO1), def456 (PO3), jkl135 (PO3), zyx098 (PO2)
CT2 might contain qrs369 (PO4), abc123 (PO4), ghi789 (PO1), def456 (PO3), and zyx098 (PO2)
So you see that some POs are split between containers, and some items are ordered more than once.
I hope that makes some semblance of sense.

george_hepworth · Answer

Kwoolridge&nbsp;&nbsp;Yes, that makes perfect sense and fits with my prior experience, so it sounds right 🙂 too.&nbsp;It may take a bit to think through all of it, but my first impression is that you need FOUR tables, at the very least:&nbsp;Container, which identifies each container by some sort of identification code or name. Only attributes of containers are in this table.PO, which are the orders from customers.PO Detail, which are the items ordered on each PO. Lists products and quantities of each item for each order.ContainerPODetail, which is a table for Containers and the item(s) in each PO Detail record.This allows you to apply the splits you describe.The ContainerPODetail table will have two Foreign Keys, one from a Container and one from a PO Detail Record. When an item is placed into a container, its Primary Key is entered into a record in this table, along with the Primary Key for the Container into which it is placed. You'll probably also need a field to identify the shipment for which that container is used because, I assume containers are re-used for many shipments (and that calls for a fifth table, by the way, for Shipments). There is no need to put POs in the ContainerPODetail table because they are related to the PODetails and need not be repeated. It also occurs to me that a PO might be split across multiple shipments, so you need to account for that possibility.I would rather keep all PO Detail items in one container, but I doubt that matches your business reality.&nbsp;Try that out first by creating the tables and populating a few records manually. See if that raises and problems with the DATA. Once it's clear it works, you can design your tables accordingly.&nbsp;I think you should separate the tasks. Create one form and subform for POs and PODetails. One to many.Create another form for Containers and a subform for PODetails. One to many, again.If a PO can be split across multiple shipments, that calls for an additional main form and subform for them.&nbsp;

Forum Discussion

Subform for Subform