Run-time error '2105': You can't go to the specified record

[Carrinmere]

So I created a database using PersonnelSSN as the PK for my parent table and then TrainingSSN, MobilitySSN, etc. as the foreign key (and PK for their respective tables) for other related tables. After I built the database, however, I discovered that Sharepoint will not recognize anything but ID Autonumber as the PK for any table exported to it which I need to do with my back end for the database to work. To correct this I added an ID Autonumber for every table but kept the relationships mentioned above. When I do that I get the error Run-time error '2105': You can't go to the specified record when my database attempts to open a new record from any form I have created. When I run the debug code it highlights DoCmd.GoToRecord , , acNewRec in my VBA script. I am at a loss at how to fix this. The DB works fine if I change every tables PK to ID Autonumber except the tbl_Personnel, but the moment I change the tbl_Personnel to ID Autonumber as the PK with PersonnelSSN as just a unique field in the table my GoTo New Record Macros and VBA scripts shit the bed. Thoughts?



Carrinmere

[June7]

If the autonumber is designated as primary key then you need to use it as such. Store it as foreign key in related tables and establish relationships on those fields, not SSN. SSN should not be a key field anyway. The SSN should reside in only one table.

[Carrinmere]

If SSN only resides in one table, what am I using to build the relationship when all PKs for each table are an ID Autonumber?

[June7]

A long integer field in related tables will store ID of parent table as a foreign key value. Whenever need to view SSN in report include parent table in query used as report RecordSource. This is basic relational database concept. The autonumber ID fields of dependent tables are not involved in primary/foreign key relationship with parent table.

[Micron]

I'd say that this is at best, a continuation of a prior thread, or at worst, cross posting

https://www.accessforums.net/showthread.php?t=84277

[ssanfu]

Without being able to see all of the fields in the tables, this is how I would start designing the tables and the relationships: