Update related forms and tables

[obs]

Hi,
I have two tables which are related in 1 to many relationship.
The first table has a primary key, and other fields, and the second table has a primary key, a foreign key (based on the primary key of the first table) and other fields.
I present the two tables in two forms which are linked by the foreign key field.
The problem is that when I have a certain value in the primary key of the first form and I want to change it, it changes the foreign key in the second form, but deletes all of the other data in that form.

Example:

first form :
primary key: a
...other values

second form:

first record:
primary key:a1
foreign key:a
...other values

second record:
primary key:a2
foreign key:a
...other values

when I want to edit in the first form the value of the primary key from "a" to "b", it updates the foreign key in the second form but resets the primary key and all the other values, so that there is only one record in the second form.
How can I update the value on the first form and update the second form and the related tables?

Hope this is clear enough.
Any help appreciated.

 

[RuralGuy]

I don't understand why you would want to change the PrimaryKey in the 1st table. Usually you would change the ForeignKey in the 2nd table to point to a different record in the 1st table.

 

[obs]

Thanks for your reply.
If I change just the foreign key in the second table, it would only change one record in the second table. I want to change the foreign key for all of the related records in the second form.

I would want to change the primary key in the first table because it has a meaningful value, not just an autonumber.
It holds a certain number which is unique for each record in the first table.

There is the first form which represents the first table and in this form I enter the number which is a primary key, and then when I press a button, it leads me to the second form, which shows all the related records in the linked table (which is linked to the first table by the foreign key).

Let me also add that although the tables are linked in 1 to many I do not use a subform, but use two separate forms which are linked by the foreign key field.

If I used a subform, there would not be a problem, but the user wants two separate form.

Thanks.

 

[neileg]

Your mistake is in having a primary key that both has meaning and which you want to be able to change.
I don't ever use meaningful PKs so I don't need to change them. If you want a user to be able to change the value of a field, don't set it as a PK.