Foreign Key's not matching Primary

[soyring]

My DB has four tables: tblContact, tblPerformance, tblState, and tblComments.

In tblContact there is a field (ContactID) which links all the tables together. It is a PK in tblContact and a FK in the other tables.

I have form that has txtboxes to enter data in each of the tables. (Except tblComments, it is a subform on that form.)

Now when a user enters data into a field that is in tblContact (say for example ContactName) ContactID will autonumber correctly. However when the user types into a field that is for any of the other tables (besides tblComments) the ContactID for that table will not populate the autonumber. It will just make a 0.

Then of course i get a message when i try to save.

In short, my foreign keys are not populating with the correct primary key value.

(FYI, my PK is a autonumber and the FKs are all just set as numbers.)

 

[KenReay]

Hi

Why not make all of the 'other' tables be updated via a sub form, like the one which 'works'

For a one-many relationship this is what you would expect. If the relationships are one-one, why not combine the tables?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[soyring]

The reason I didn't make everything subforms is because of the way that would look.

As for breaking things up into different tables, it just seemed more logical to me. I have done so on other dbs and have not had any issues. Originally this was working when i had it set up as an INNER JOIN, but that didn't return all the items in the set. When i switched to LEFT JOIN is when the problems started.

 

[KenReay]

Hi

I am not saying it is wrong to break your data up into 'n' tables

What I am saying is that if the design requires this 9ie one to may relationships), then it also follows that that the conventional Mainform (for the one table) and subform(s) for the many table(s) is the way to go interms of maintaining the tables

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK