Form with multi field primary Key table not working

[MSAccess_newbie]

Hello All,

I have a form which reads from two tables linked with multiple keys in a one-to-one relationship.

Primary Key is based on four fields as follows:
ID
Year
Submission
Version

On the form I only show (use) ID, since the users don't need to see the other Primary Fields.

The form should allow users to edit records.
The problem is when I edit values in one record, the value is also changed in the other records with the same 'ID' even though the other components of the primary keys are different. I don't know why its doing this. any clues would be great. Thanks

 

[MSAccess_newbie]

Now here's something weird...

When I input a value on the front end, it sets all records with the same 'ID' to that same value. However when I look at the back-end table, only one record is updated (which is what I expected in the front end).
Problem is here that the record that's updated is not necessarily the same record I chose on the front end... for example.

On Front end when I update field 'Sponsor' to be 'Enterprise' for Record
With Primary Key:
ID: 9788
Year:2012
Submission:2+10
Version:1
All records with ID:9788 have Sponsor field updated to 'Enterprise'.

However when I look at the backend I see only record
ID:9788
Year: 2012
Submission:2+10
Version:3
has the updated Sponsor...

So it seems to be updating a record with the same ID at random. Anyone know why I'm getting this behavior? Does it have to do with my recordsource?

 

[rodmc]

Have you tried making the other parts of the PK visible? If not how do you know its not just updating the one record, cos your only seeing one part of the key ie the ID

 

[MSAccess_newbie]

Hello rodmc,

Yes I tried it after making all the PK fields visible. It does the same thing exactly.


"If not how do you know its not just updating the one record, cos your only seeing one part of the key ie the ID"

I can see it because the table lists all the records so I see several records with the same ID and they all update to the value I set in any one of them.
It looks like this...

ID Project Name Sponsor
123 ProjectA Enterprise (Enter value in this one)
123 ProjectA Enterprise (this one is changed too)
123 ProjectA Enterprise (this one is changed too)

 

[rodmc]

What I mean is unhide the fields on your form until you fix the problem, that saves you jumping back and forth between forms and tables to check the data.

Why do you need a one to one relationship?

can you post your db in ac2003 format? (.mdb)

 

[MSAccess_newbie]

Hi Rodmc,

Good idea to show them, for troubleshooting its easier...

I have a one-to-one relationship because one table called 'projects' has all the projects
basically contains the Project ID Project Name, description etc....
The other table contains the Project categories. Since the project Names and descriptions might change from one version or submission to another I chose a one-to-one relationship between both tables...does it make sense?

Im using Access 2010, and one table has a calculated field (I don't think that works on 2003) I could modify the table and post it in 2003 format. Will try and do that by tomorrow InshAllah.

 

[MSAccess_newbie]

Hello,

HYG, I've attached my DB here after converting the linked tables to actual tables & removing a lot of the irrelevant forms & tables.

Somehow now the problem of editing all same ID records is no longer there but what I'm seeing is two identical records of the same ID on the 'Local' Form. The 'Projects' form now works fine .

Note: the attachment is in 2010 format as I wasn't able to convert it.
Even after removing the calculated field when trying to save to 2K3 format It gave me an incompatibality message and deleted my forms!

I hope you can still open it.