Error Message

[harryfraser]

Hi,

When I add a new contact to an organisation I get the error message:

You cannot add or change the record because a related record is required in the table tblInstitution.

This only happens if I add a new institution/organisation and then add a new contact to that organisation. However if i just keep clicking ok it eventually lets me...

Any Ideas?

Have tried looking on a previous thread but all the things it has suggested i have already done.

Thanks

Harry

 

[jdraw]

What are your table structures?
Do you have related tables?
Can you attach a picture of the relationship view?

 

[boblarson]

I'll bet that you have done one or both of these:

1. In your main form's record source you have included the table which is for the subform records.

2. In your subform you have included the table that holds the main form's records.


To fix this, remove the appropriate table from each of the recordsources.

 

[harryfraser]

Hi Bob,

Neither of these i'm afraid, unless you're referring to the primary key/foreign key? In the contacts table I have the primary key for the institution (i.e. institution name is the foreign key in the contacts table)?

Any other ideas?

Thanks

Harry

 

[boblarson]

Hi Bob,

Neither of these i'm afraid, unless you're referring to the primary key/foreign key? In the contacts table I have the primary key for the institution (i.e. institution name is the foreign key in the contacts table)?

Any other ideas?

Thanks

Harry

Institution ID should be the foreign key in the contacts table NOT the name. The primary key for the institution in the institution table should be a numeric key of InstitutionID (normally an autonumber surrogate key). Then in the contacts table the same field InstitutionID would be a long integer.

 

[harryfraser]

Hi Bob,

Sorry I'm a bit confused by this. Do I have the foreign key as a lookup field? I also made the institution the primary key as it is unique - why do I need an autonumber in this case? Isn't the purpose of the primary key to be a unique identifier?

Just to clarify I wanted the user to be able to add a new contact to a pre existing institution. I also want the user to be able to upload contacts from Exel, which I dont see how they can do this if the foreign key is simply a number rather than the name of the institution?

harry

 

[jdraw]

Harry
Suggest you do as Bob advises and use the autonumber InstitutionId in the Institution table, and long integer foreign key in Contact.

If you really want to see more about Natural and surrogate keys look here
http://www.techrepublic.com/article/the-great-primary-key-debate/1045050