This is difficut to explain so I loaded some screenshots that I hope will help illustrate.
I've created a database (from scratch by scratching my way through) that has a table dependant on another table. The jpeg "WOINFO" shows a table with 'WOSN' as a primary key. The jpeg "DEFINFO" has a field for 'WOSN' which autopopulates (just as I'd like it to) from data typed into "WOINFO". The primary key for "DEFINFO" is the auto ID.
HOWEVER, there is also a column in "DEFINFO" for 'Evaluator' which, no matter how hard I try, will not autopoluate from "WOINFO".
There is a jpeg of the "Relationships" screenshot as well.
Please ask whatever questions you need. I have tried everything I can think of. Thank you VERY MUCH appreciate the help!
Your table structure needs some work. A Primary Key is used to uniquely identify a record within a table. By adding an Auto ID to each table you have defined a Primary Key. But tables should be joined on the Primary Key.
The Primary Key should be joined to the Foreign Key of the other table. For instance your Defect Categories table links from the DefectCategory field in Defect Categories to Defect Information. You have two choices:
1. Change your Primary Key to DefectCategory in Defect Categories and remove ID (assumes DefectCategory in the DC table is unique)
2. Join using the ID field to the DefectCategory field in the Defect Information field
Another suggestion I would make is to rename the ID fields (if you plan to keep them) a suggestion would be CategoryID, DescriptionID, StampTypeID, etc.
But I think the reason you are having a problem is the link between the Evaluator fields in the WOI table and the DI table. The DI table should link to the Evaluator table.
I will certainly attempt to follow your guidance, thank you. One thing I should mention is that the main form (where folks enter the data) is made from the WOI table and the DI table is made into a subform within that primary form... so when someone enters a WOSN number in the primary form, the number needs to be unique (no duplicates). But within that WOSN there may be multiple errors (hence the subform). So for the DI, the WOSN cannot be unique. Does that make sense? Should I make it a primary key with duplicates OK? Will that help with the evaluator name problem?
It is impossible to make a primary key that is not unique (DuplicatesOK).
The subform should be using a related table that has a foreign key in it that links back to the primary key (WOSN) of the primary table (WORK ORDER).
You really need to get your tables designed correctly before going further. What is the point of the ID field in WORK ORDER?
There is also no point of having the Evaluator in the DEFECT table since there is one already defined for the work order... either use the evaluator in the WORK ORDER or this field has to be related to the EVALUATOR table, instead of the WORK ORDER.
Too hard to tell because your table design is a bit of a mess.
I think you need to re-do a large portion of your data base schema. Let's start with this:
1. Remove all the relationships you currently have.
2. I suggest you name your work orders table "WorkOrders"
3. Name the primary key of the work orders table "WorkOrderID"
4. Name the primary key of the Evaluators table "EvaluatorID"
5. Add a field named "EvaluatorID" to your WorkOrders table. This is the foreign key to the Evaluators table.
6. Remove the field named "Evaluator" from the WorkOrders table.
7. In the relationships window, create a one to one relationship between EvaluatorID in the WorkOrders table and the primary key (EvaluatorID) of the Evaluators table.
Proceed in a similar way with the rest of the tables and keys.