Index or primary key cannot have a null value

[cbellew]

Hello again,

I have a problem with one-to-one relationships (with tables :p). I have seperated a large table into four smaller tables - EmployeeDetails, MRSADetails, DiseaseHistory and NeedleStick. I have made sure all of the tables contain the same number as a primary key in an 'id' column. I have made one-to-one relationships with referential integrity, cascade delete and cascade update enabled.

I have made a form with the record source set to EmployeeDetails and have included subforms for the other three. The forms are linked correctly and using the navigation buttons down the bottom i have ensured they are all displaying relevant details. The problem comes when i wish to enter a new employee (using the arrow with the * navigation button at the bottom). I immediately get an error message saying "index or primary key cannot have a null value".

Any help would be appreciated as always!

[PianoMan64]

The reason that you are getting that error message is because when you go to add a new record into the main form, there isn't one in any of the other three tables that you have linked together. You need to be able to pass that value to the other table so that you can add records accordingly. Since you are using the same ID for all tables you're going to have that problem until you remove all that referrencing that you have setup and do it through VBA to make sure that when each section is filled out, that you pass the correct RecordID to each table to maintain the relationship between all four tables.

Personally this is a very bad design because it makes it very very easy that if the application crashes, that you loose the data integraty.

If the reason that you've split the table up to 4 tables was because of size, then I would think about going to another backend platform like MS SQL or MySQL or Oracle. This way you don't run into that 2gig limited that is placed on MS Access tables.

Hopefully that helps,

Joe P.