changing primary key field and related data

[ssh]

I can edit all data when I select or type a value from primary key combo box. But how can I edit primary key value itself so it will apply all related data? I have a form with a subform. I tried to duplicate a record but of course duplicate value errors appeared. I think an additional text field for a new pr. key value entry would be nice?

 

[Rich]

Your best option is not to edit them at all, more especially if they have related child records

 

[ssh]

Why so? The only way to modify is only through tables?

 

[Rich]

I don't really understand what you are trying to do, why would you want to duplicate a PK?
Are you using a subform for one to many related records?

 

[ssh]

I'm trying to modify pk itself and all related data. One to many.

 

[Rich]

It's a dangerous move to say the least and a good case for using auto numbers for pk's. You'll have to use an update query to update all the child records to the new PK. Make sure you back up your data first and check of course that the updated records are correct

 

[Rich]

You have more faith in Access than me Pat

 

[ssh]

And how do I do that, 'You can choose the option to enforce referential integrity'?

 

[Howlsta]

Open the relationships window, it's a symbol on the toolbar with three linked tables or Tools|Relatioships.

You must define relationships for your tables. If you haven't done this before none of your tables will be linked, so 'show' your tables that need to be linked.

Join your tables that need to be linked by dragging from the primary key to the foreign key in the other table. The join line should have the number 1 at one end and infinity at the other (foreign key side). Double click the join line and you will be able to edit the relationship. Tick enforce referential integrity and cascade update related field. By setting the cascade option to this if you change the primary key at the '1' end the 'many' end's foreign fields are also updated.

HTH

Rich