CODEWITHSUNDEEP
ms-accessasp-classic
Adam
Adam

Reputation: 77

You cannot add or change a record because a related record is required in table 'table_name'

I just took over access website and I am trying to figure out why when i try to update a record in one table, it provides me with this message.

"Microsoft JET Database Engine error '80040e21'

You cannot add or change a record because a related record is required in table 'Members'. "

How do I find out what the related record is.

Thanks for any assistance

Upvotes: 3

Views: 68517

Answers (5)

Nikolas
Nikolas

Reputation: 1

I managed to solve this problem. As it turns out, it occurs due to the use of autoincrement in the field you are referring to. Instead, use INTEGER NOT NULL. And if you need to use auto-increment, then you can query max(id) in the table and increase it by one when adding a new row.

Upvotes: 0

NewSites
NewSites

Reputation: 1739

I have encountered another cause of this problem.

Call the two tables A and B. They have a relationship defined between them with referential integrity. When I tried to add a record in B that had a corresponding record in A, I was getting the error "You cannot add or change a record because a related record is required in table 'A'". It made no sense because the required, related record was there in A.

Then I realized that a field in B had its Display Control* set to a list box whose source was A and the bound column was the wrong column number, which was part of the primary key. So when I tried to add a new record in B, it looked in the wrong place for the value of primary key I was adding, and of course it didn't find the necessary value there. When I set the bound column to the correct column number, I was able to add the new record.

'* Display Control is in the table's field properties under the tab Lookup.

Upvotes: 0

shieldgenerator7
shieldgenerator7

Reputation: 1756

In my case, I had a combobox which was changing the name of a record from another table that required an id of yet another table.

So I had Table C, which had a foreign key to Table B, and Table B had a foreign key to Table A. My combobox was set to control the name field of Table B, so I changed it to control the foreign key in Table C (that points to Table B) instead.

To fix my problem, I selected the combobox, went to the 

Property Sheet (F4) ->Data->Control Source

and set it to the foreign key field in Table C (that points to a record in Table B)

Upvotes: 0

Adam
Adam

Reputation: 77

Thank you for your help. I was able to get it working by using some of the advice above and through trial and error.

The problem was there were two related fields, and that is why when I tried to do what I expected would be required it did not work properly.

Upvotes: 0

Stuart Allen
Stuart Allen

Reputation: 1592

The issue is not finding the related record, the issue is that there is no related record in Members corresponding to the record you are trying to update in the other table.

You can start by looking at the relationships that are set up for your database. Go to the Tools menu and select Relationships. Look for any relationships between the Members table and the table you are trying to update. Look at which field(s) are used in the relationship(s). This will tell you which field is causing the problem in the table you're trying to update. For whatever value you are trying to enter into this field, the same value must first exist in the related field in Members.

Upvotes: 5

Related Questions