Parent - Child Forms (1 Viewer)

[sheckay]

Hello. I have been trying to figure this out, but no matter how much I research it or how many attempts I make, I can't figure it out. What I'm trying to do is work with 2 tables. The first table is very similar to the other 2 tables, that only difference would be that it includes the customer name and PO. There are other fields in there besides that. These fields aren't identical to the fields in the other two tables. But they are not customer related. They're order related. Just different processes of the order.

I'd like to create whatever relationships that need to be created. I'd tell you all that I tried, but if I did this would be a really long post. And then I would like to create a form based off of table1. Use that as the parent form. Make child forms out of the other 2 forms. Then make the form much bigger and then put twp child forms in the main form.

I'd like to have it so that as I enter info into table1, I can then enter data into the child forms and have them connected to the parent form.

Can someone help me out with this?

If necessary here's a little bit of what the forms look like field-wise (the fields in red are primary keys)

Form1 Form2 Form3
CustomerId ProcessID InspectionID
CustomerName DateRequired InspectedMeasure
PONumber SpecialREquirements AcceptedYes
ConditionIntact OperatorSignature AcceptedNo
ConditionDamaged SetupSignOff InspectedCut

Thanks in advance.

 

[The_Doc_Man]

From what you showed us in the fields, you have nothing obviously in common and therefore cannot establish parent/child relationships - because if there is nothing in common, you can't develop ANY relationships.

From your verbal description in your first paragraph, I think you must be missing at least one table to act as a junction between the customer or PO and the other tables. I cannot tell from what you posted how the forms and their contents would interrelate, and that means that something is missing. I.e. if this process you are trying to program via a database is actually a working business, then there ARE elements in common on which to base the business. But it isn't clear to me.

This is where one of my "old programmer's rules" applies. Old Programmer Rule #1 - IF you can't do it on paper, you can't do it in Access. Meaning that if you can't draw a diagram of the data flow and/or work flow of this process, you don't understand it well enough to be writing code or tables or anything else. Start from the "real" process that you have to do by hand (or at least by some other method). Look at what you track and what you seek when tracking. Be sure that you commit your findings to paper. The idea is that once you have a paper drawing of what it is you are doing, you can start to analyze steps and understand relationships. If you can't build that diagram, you aren't ready to do anything else.

 

[sheckay]

Hello DocMan. Thank you for your reply.

I only want to create records that start with the creation of a record in table1, then after that's done a corresponding record would be created in table 2, and once that record is done in table2, a corresponding record would be created in table3. The records in both table2 and table3 are connected to the customerID field in Table1.

If I was to add a foreign key field into into tables 2 and 3, would that allow me to build a relationship?

Form1 -----------------Form2 ----------------------Form3
CustomerId ------------ProcessID -------------------InspectionID
CustomerName -------CustomerID------------------CustomerID
--------------------------DateRequired ---------------InspectedMeasure
PONumber ------------SpecialREquirements- ------AcceptedYes
ConditionIntact -------OperatorSignature ----------AcceptedNo
ConditionDamaged--- SetupSignOff ---------------InspectedCut

 

[arnelgp]

I only want to create records that start with the creation of a record in table1, then after that's done a corresponding record would be created in table 2, and once that record is done in table2, a corresponding record would be created in table3

imho, you need to concentrate first on table build-up.
you will be needing, at least:

1. customer master table
2. jobs/services you are offering.
3. the "standard" steps/process needed to complete a specific job/services.
4. the job header file:
which include the

JobID (autonumber)
customerID
date the job was created
status
PO Number
job service you are offering

5.job detail file:

DetailID (autonumber)
JobID (FK, long)
the standard "steps" (each on separated record)
date started (date)
date finished (date)

6. inspection table for job detail:
InspID (auto)
DetailID (FK, long)
InspNumber (short text)
InspDate (date)
Inspector (short text)
Result ("pass", "fail")

*****************
access is not automatic.
so creating customer will not automatically create a PO/Job for that customer.
you need to do that in the Job/Order Creation form.
you will then manually "insert" each "steps/activity" to your job detail file.
inspection records will be entered manually.

 

[The_Doc_Man]

I return to my explanation. A foreign key added without context has no value.

You've got these three forms discussing three different records. How are the records related to each other in the day-to-day performance of your business? If you had to go to your paper-based file cabinet, how would you be able to know that this particular inspection (paper) form was related to this particular job (paper) form? And how would you identify the customer for that job? Can you even discuss how to link the inspection to a customer?

It is answering questions like those that lead to establishment of relationships. Your answer didn't seem to convey that. Instead, you focused on creating a couple of foreign keys. Not to dismiss you, because the FK fields probably WOULD be the way to implement it. BUT... screw the fields. What is the real-world way that you would inter-relate those records. Answer THAT question and THEN you can talk about fields and what to store in them. Proper design always always always starts from the real-world data. And no, I'm not stuttering.

We emphasize that data/process relationship because of an unwritten rule that we have to write now and then to remind ourselves. The tail does not wag the dog. If you create a linkage in the DB that doesn't match reality, one day someone is going to trip over it and wonder why they need this particular linkage. (Maybe even you, when something breaks a year from now and the boss says "FIX IT".) Your goal is to implement a data tool that follows the reality of your business. You don't create anything as a convenience for DB operations - you create something because your business operations require it, or because Access requires it to do something based on business ops. An example of the latter is that to have a true one/many relationship with relational integrity assurance, you must have a unique index on the field on the one-side of the one/many relationship.

I'm pounding on this because Niklaus Wirth, the "father of PASCAL", was fond of saying that 80% of all application failures could be derived from the consequences of incorrect or incomplete data design. And I believe he was right.

 

[Pat Hartman]

There are very few situations where it makes sense to have one record cause the creation of a dependent record and this is not one of them from your description. Doc's advice is solid. Tell us in words what the workflow is.

1. Are there multiple processes? If so, you need an additional field in the process table to identify what this process is.
2. Unless you are recording multiple inspections of the same process, the inspection fields can go into the same table as the process fields because they have a 1-1 relationship.

When you create a form with a subform, If your tables have defined relationships (in the relationship window of the BACK END database), then Access will automatically set the parent/child links. Otherwise, you need to set them. Once set, Access will automatically insert the parent record's PK as the FK in the child record based on the fields you named in the master/child links.

 

[sheckay]

I return to my explanation. A foreign key added without context has no value.

You've got these three forms discussing three different records. How are the records related to each other in the day-to-day performance of your business? If you had to go to your paper-based file cabinet, how would you be able to know that this particular inspection (paper) form was related to this particular job (paper) form? And how would you identify the customer for that job? Can you even discuss how to link the inspection to a customer?

It is answering questions like those that lead to establishment of relationships. Your answer didn't seem to convey that. Instead, you focused on creating a couple of foreign keys. Not to dismiss you, because the FK fields probably WOULD be the way to implement it. BUT... screw the fields. What is the real-world way that you would inter-relate those records. Answer THAT question and THEN you can talk about fields and what to store in them. Proper design always always always starts from the real-world data. And no, I'm not stuttering.

We emphasize that data/process relationship because of an unwritten rule that we have to write now and then to remind ourselves. The tail does not wag the dog. If you create a linkage in the DB that doesn't match reality, one day someone is going to trip over it and wonder why they need this particular linkage. (Maybe even you, when something breaks a year from now and the boss says "FIX IT".) Your goal is to implement a data tool that follows the reality of your business. You don't create anything as a convenience for DB operations - you create something because your business operations require it, or because Access requires it to do something based on business ops. An example of the latter is that to have a true one/many relationship with relational integrity assurance, you must have a unique index on the field on the one-side of the one/many relationship.

I'm pounding on this because Niklaus Wirth, the "father of PASCAL", was fond of saying that 80% of all application failures could be derived from the consequences of incorrect or incomplete data design. And I believe he was right.

Thank you for your replies, and all the info within. The way that the tables would connect with each other would be starting with the customers table (customerID (primary key), city, state, etc) there would also be an incoming table with an IncomingID as it's primary key. The CustomerID of the Customers Table would be its foreign key. This table would record the receipt of shipments, details of received, measurements. The next table would be the processing table. It would have a processID as it's primary key. And it would be connected to the Incoming table via a "incomingID" foreign key. This table would include specs of the order, machine used, operator's signature, measurements, ship out info, and the inspection fields (thank you Pat).

To answer Pat's other question, there's other processes that are related to the same order. I would expand the process table to include them. That shouldn't be a problem or affect the design in terms of relationships.

Right now, I have things designed as I described above (in this reply). I have two parent forms, that have child forms. There's the Customer Form, it's child form is the incoming form. The incoming form is also a parent form. The process form is the child form of the incoming parent. Each form setup works great. When I open the customer form I can start a new entry. After the customer part I can enter the incoming info within the subform. When I go to the incoming parent form, I see what I've already entered, and I can enter additional data into the process subform.

Is there a way to create a form that can use to browse existing data (within all 3 tables) and to also enter new records into all three tables? I've tried table based on an append query, that queried all 3 tables. But that doesn't seem to work.

Thanks in advance.

 

[mike60smart]

It would help if you could upload a zipped copy of the database?

 

[Pat Hartman]

Depending on the size of each form, you can have several subforms or sub-subforms all snyc'd on one main form. Below is an example. The form on the left is a list which is filtered by the options at the top of the form. When you click on one of the rows in the list, the detail form for that record shows to the right. That form has a subform that shows below it. Everything is controlled using the master/child links of the subform controls.