Subform data clears after entering data into main form

[DarkSilence1979]

Hello everyone.

First time poster here. I am running into an issue with a continuous subform on one of my forms in Access. The continuous subform is based off of a query and I just dragged and dropped it into my main form. So I let Access link the Master and Child fields together. So here comes the issue. This form is for entering a new record. So the form opens to a new blank record. I want to type data into the subform first, so I type in my data and it goes to the next record in the continuous subform. So I can continue to enter more records, or finish there. So then I click onto the main form to enter more data to complete the final record. But the second I start typing data into the main form, the data in the subform clears and saves to the table related to the subform without an ID link to the main record. So when browsing through my main records, the subform data does not show up since there is not a number in the table of the subform to link to the main form. Now what I have noticed is if I enter data into the main form first and then enter data into the subform, it works correctly and the link is established. So I left my ID numbers on my form so I could see what is going on behind the scenes, and this is what I have come up with. When typing data into the subform first, it does not create a primary key number in the main table, just its own table. So once data is typed into the main form, a primary key number is generated, it clears and save the data in the subform without a linking foreign key. So I need my form to generate the primary key once I start typing in the subform so it can save the foreign key in the subform table before I start typing into the main form.

I really hope that makes sense. I have searched on this answer all over and I cannot seem to find anything exactly like my issue. Looking forward see what to community has to say. Thanks everyone!

[Micron]

I've never seen this behaviour, but that's because I wouldn't try to create a child record when there is no parent for it, which is what you seem to be doing. If you're saying the main form has no data when you're creating the child record, then you're doing it backwards. I'm surprised it even lets you. You should be getting an error, but I can't recall what the message is. The result of said error is that you're not allowed to create the subform record without a parent record, so something seems wrong with the setup.

[Welshgasman]

Seems like referential integrity is not set?, that would give the error message, cannot save child without the parent record?

Anyway you are going about it in completely the wrong way.
You create the parent record, first, then the child.

[DarkSilence1979]

Would wanting to enter data into the subform before entering data into the main form have something to do with this issue? Referential integrity is set. I know this one has me scratching my head. I have even deleted the subform and added it back in, but get the same results.

[orange]

DarkSilence1979,
Welcome to the forum.

Can you show us your tables and relationships (relationships view)? Also show us what "Access" has determined your LinkFields to be. Form/subform is typically for data in a 1:N relationship. As in nature you need a Parent (1side) record to have child record(s). The situation you describe is often the result of trying to add a child record which has no Parent.

[DarkSilence1979]

Orange,

Here are the pics you asked for.

[Bob Fitz]

You show both Link Fields as Po_ID but only one table has this field?
EDIT:
Your picture is unclear but perhaps they should be ME_ID and MeID?

[Micron]

This form is for PO and PO details? Not clear yet.
Then there should be a relationship between PO_ID in PO detail and PO_list tables. That's why you can start a detail record with no main record.

Several other things might need changing as well. If multi comments for a PO then comments field shouldn't be there. OpenClosed looks like a yes/no field; better to have ClosedDate, then if null it's open. A date provides a closed date. Yes/no provides little information.

[DarkSilence1979]

@Bob,

tblME is just a list of measures of equivalences. Like EA, PKG, BX, FT, IN,etc. It is used for product quantities and tblME fills in the combo boxes used on my forms. So I would not think that would be causing my issue.


@Micron,

tblPoList is the main table. It is fed data from the other tables, like companies, charge numbers, parts, etc. tblPoDetails is the table that stores the line items in a purchase order (PO). So from following a video on a basic order database I came up with the PO form with a subform. The subform would have nothing but the line items and be stored in the tblPoDetails. Also the subform is based off of a query using tblPoDetails. Then in the main form would have company, charge number, PO number, notes, etc. So again I would like to put in the line items first, which is in the subform, then enter all the other data in the main form. But once I loose focus on the subform to start entering data into the main form, the minute I start typing in the main form data, the subform saves the line items, then clears the subform ready for a new record without getting Ponum_ID into the tblPoDetails to link the record in tblPoList to the record or records in the tblPoDetail. Only if I enter data into the main form first, then the line items and hit the save button, does everything work the way it is supposed to. It seems to be key that the Po_ID primary key in tblPoList need to be created first before the line items in the subform are created in tblPoDetail. The link that Access makes when dropping the subform into the form is in the picture, Po_ID to Po_ID. So Access sees the relation even if there is not a relationship line connecting the tables in the relationships view. I find that weird but ok, I'll buy that. I have an edit form that is built the same way as my new PO form, but it is used for editing and viewing created POs. And as long as there is a correct Po_ID number in the tblPoDetail linking to the tblPoList, the subform shows the correct line items for the main PO. Maybe I need to look at my relationships, query, tables? I hate to just have to start from scratch if it is something simple I am missing.

[Micron]

I get all that, and unfortunately, nothing is different. You are doing it backwards - plain and simple. The fact that you can do it at all is because the relationship between the tables is wrong as you have it. In fact, you have no relationship at all between the 2 very things you're trying to relate. You should be joining POID to POID. But you will not be able to input line items (the many) if there is no parent record in the main form. To want to do otherwise makes no sense. If you absolutely must, IMO you will have to use staging tables that are not joined and use code/queries to get data into the primary tables correctly. That's like building a Rube Goldberg machine.

[DarkSilence1979]

It looks like I will need to play around with my tables a bit with the relationships, or just try building a new purchase order form from the ground up with a subform and maybe it might work...

[Micron]

Did you research how to create form/subforms? I'll bet there's a gazillion vids and written tutorials. PO and PO Line must be one of the most common setups there is. Then there are templates (not always well designed) but you could always read and create, then start a new thread to have your design evaluated. Be prepared for questions wrt the process that the db supports as it's often vital in order to provide focused answers.

[orange]

It looks like I will need to play around with my tables a bit with the relationships, or just try building a new purchase order form from the ground up with a subform and maybe it might work...

No! Better to describe the business process(es) you are trying to support with this database. Tell us in simple, plain English - the who, what, where, when, why, how much and how often - overview of the "business" or a day in the life at our organization. Get the requirements understood and vetted before getting too deep into physical Access. You'll get more focused feedback once readers have some context for your issues.

See this link.

[ArviLaanemets]

Looking at your relationships picture, you need:
Table for purchase orders, like
tblPurchaseOrders: PO_ID, PO_Num, PO_Date, Supplier_ID, ...;
tblCompanies: Company_ID, ... (Company_ID is used as FK Supplier_ID in tblPurchaseOrders);
tblPORows: PORow_ID, PO_ID, Part_ID, PartQty, ... (you need a separate table for parts info - part description must not be in this table at all);
tblParts: Part_ID, PartNo, PartDescription, ...;
the rest of your tables (they aren't essential here).

In your app, you need a single form where you register purchase orders. In this form, you need a continous subform where you register purchase order rows. For this subform (NB! For subform, not for continuous form based on tblPORows, which is source object of subform!), you set LinkParentProperty equal with PK of tblPurchaseOrders (PO_ID in my example), and LinkChildProperty equal with FK of tblPORows (in my example also PO_ID).

Now, whenever you add a row in subform, it is automatically linked with active purchase order in purchase orders form (i.e. PO_ID in tblPORows is set automatically same, as PO_ID in tblPurchaseOrders. And you better set the control linked to FK in subform invisible, because there must not be any way for users to edit this control!).

[DarkSilence1979]

@Orange,

So what this database is for is creating/tracking/reporting local purchase orders for work. This originally started out as a Visual FoxPro database, but since FoxPro is not supported anymore, I needed to find a more modern program to use. So for ease I will try to explain most of the main points. Someone at work wants something. I will make a local purchase order for said item. I open the New Purchase Order form to input all the data. Line item details for each item needed into the subform, then in the main form will enter where purchased from, the charge number to pay for the item, and a category for the item. The subform is the line details which consists of a part number, description, quantity, measure of equivalence (which is a combo box linked to a table), and the cost. The subform is linked to a query. Then all the other info is entered into the main form. The company where we purchase the item from (also a combo box linked to a table), charge number for charging the price to (again a combo box), and then category (combo box). There are various other events happening with VBA on the save and exit buttons.

I did some more testing on my new purchase order form, and I noticed that having the primary key textbox on the main form, (which is Po_ID, the linking fields of the master and child) would make the subform clear like stated above, but if I would delete that textbox off of the main form, the subform would not clear. So not sure why that would have anything to do with clearing the subform, but I am still having the problem that the Po_ID primary key number will generate in the main table, but it would not generate that Po_ID foreign key into the child table. So still confused as to why Access is not making that link. Does anyone think that because I drag and dropped the subform into the main form and access made the link, that has something to do with it? Should I just make a subform in the main form with the subform control?