Copy Records (Primary Key issues)

[Blorf]

Hello.

I may be a victim of my own poor design here, however... I built a job estimater for my sales folks, and it uses auto numbers as primary keys.

Now, they ask how can they copy an estimate to another estimate so they can tweek it, and I am not sure, with out writing a bunch of code.

Problem is say table1, with autonumber, relates on that autonumber to table2. Table2 has it's own autonumber, that relates to table3.

Knowing that, is there some method out there for copying records and keeping the relationships intact?

I hope I have made sense here.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[lupins46]

I think you are into some significant redesign and what you are asking for cannot be achieved without using code. It sounds like you already realise this but like all of us you're just hoping that there's some magic way out of the hole we've dug for ourselves.

You have to stop using the autonumber field as the linking field from your main table to the second table.
You could introduce a second number field for your link field and a generation number of some sort to distinguish between 'tweeks'. This new number field would stay the same for each tweek of the same estimate,and the generation number would increase by one for each new tweek. The primary key would then be the combined estimate number field and generation number field.

The control of the generation numbers would have to be done in code as you would need to find the highest one used so far for each estimate number and add one to it.
You need to distinguish clearly between creating a new estimate and creating a new tweek otherwise you users will just mess things up. You will also need some code to generate your estimate number for new estimates. Maybe on a 'tweek' record there might be some fields from the original estimate that cannot be changed and these need to be locked.

However, if you'd started fom here, you would have a different design completely. You would have an 'estimate header' containing the fixed info for the estimate; the variable elements of the estimate would go into a separate table so that you could just create new 'tweek' records as you wanted.

 

[Blorf]

Thank you Lupins.

I am aware of my design flaws and know that I probably should fix it up. I was sadly pressed for time and built this thing in about half a day.

I had actually forgotten about it, but now know it's what the sales force live and die by. Never expected it to be used this much. Again, my own fault here.

Another issue sadly is that I am using replicated copies on lap tops throughout the empire, so I think I am going to be stuck writing code to solve this, rather than making the design change to the tables, because the replication has to continue to work.

Either way, a lesson learned.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.