>>
my Job parent table, a typical JobNo string PK might be "UR-56342A" or "4033X-01", "4033X-02" etc., and the child JobItems table records will have a String FK JobNo field also that contains the same value. Although there is an AutoInc integer (ipkey) assigned to each parent record, I generally navigate the forms and map the children by way of the string JobNo field>>
>>is fine. Except the mapping of children should be done by the hidden Pk/Fk. It is recommended that when updating or adding or linking parent and child records, it actually be done via the hidden key fields in tables. Hence your search for the typed JobNo and get the PK for the record and then go from there behind the scenes would work and I've seen it done that way. As long as the JobNo field on the parent table set to
not allow duplicates?
>>
>>Are child records for one job ever moved to a different job (parent)?
>
>Indeed, the PK/FK will be refactored to be based on the hidden Integer (AutoInc on Parent).
>
>You asked, "Are children moved"? They are sometimes, and that just involves changing the FK, right? Currently, I change the string field JobNo on the child to the new string Parent. After re-write, I will change the child integer FK field (ifkey) to the Integer PK (ipkey) of the new Parent. Right?
If you set up referential integrity on your tables and set it to CASCADE, the change would be done automatically by the database itself and you would not need to manually re-point.
If it's not broken, fix it until it is.
My Blog