new primary key (1 Viewer)

[lipin]

I have run into an unforseen problem
I had two tables, one for the main form and one for the subform.
A field that was the primary key in main table, had relatiohship to same field in sub table. Turns out that field can sometimes be duplicated in main table. Can I add a "Now" to the field or a field that defaults to "Now" and use the field I was using and this new "Now field" as a composite primary key? The original field was a Carton Number. So Can I just create a field that =Carton Number+Now. Is this feasible? if so, do I change in table or in form? Thanks

 

[Pat Hartman]

Adding Now() as a second field in your pk would certainly make the pk unique but you really need to determine the situation that causes the duplication. Your users must have some way to distinguish between the two cartons. THAT is the field that should be added as the second field rather than a time stamp. Is the duplication an anomoly? Can you just ignore the duplicate?

If it turns out that you need to support duplicates, I would suggest going with an autonumber pk and using carton as a non-unique index. To convert the existing data, you'll need to create a new carton table with an autonumber pk and append all the carton data to it. To get the new foreign key into the related table, add a new column to hold the fk (in a later step you'll remove carton). Join the carton table to the child table and make an update query that updates the new pk field in the child table by copying the value of the autonumber from the carton table. Remove the current relationship. Create the new one. Don't forget to check enforce RI and Cascade Delete. Once the tables are back together, you can remove the carton column from the child table. You will then need to modify all your queries and possibly some other objects depending on your needs.

Adding a second field to the existing pk won't make the process any easier. You'll still need to do everyting I said, you'll just be working with different fields.

This is such a big job, I would strongly advise more investigation before proceeding. Don't forget to backup at various stages and you'll need to retest EVERYTHING thoroughly.