Forum Discussion
Linking data on tables
In a relational database table, it's crucial to have a Unique ID field in every table. This field is the Primary Key. It is used only internally by Access. Your users should not see, or even be aware of its existence.
That is usually the AutoNumber which you mentioned, although other methods of assigning or creating that unique value for a Primary Key are available. I'd recommend sticking with the AutoNumber.
Your other unique code is for public consumption. Users see it. It is used outside the database application. You generate the values for it either externally or via a VBA function. Apparently you create and assign these unique codes externally. When you add them to the table, you can create a Unique Index on that field. It's not the same as the Primary Key, but giving it a Unique Index ensures that no two people will have the same code.
When you relate records in two different tables, you use only the Primary Key. It is stored in the related table, where it is designated as a Foreign Key. YOU DO NOT REPEAT THE OTHER, UNIQUE CODE IN THE RELATED TABLE. It's not necessary, and it could quite readily lead to other problems, so don't try.
In other words, you do not need, or want, "two bits of information in every table" in a properly designed relational table. You use only the Primary/Foreign Key relationship. If you need to display that Unique Code anywhere else, you include it in a query that joins the two tables.
Generally speaking, when working with one-to-many tables in Access, you use a main form/subform design. The one-side table is bound to the main form. The many-side table is bound to the subform. And the subform control has a property, Master/Child linking fields, which handles the foreign keys in the records in the child table in the subform.
Again, repeating your UniqueCode in two different tables is not only unnecessary, it would be counter-productive in the long run.