CODEWITHSUNDEEP
ms-access
sigil
sigil

Reputation: 9546

"You cannot add or change a record because a related record is required", but related record exists?

I have two related tables, results and userID.

results looks like this:

+----+--------+--------+
| ID | userID | result |
+----+--------+--------+
|  1 | abc    |    124 |
|  2 | abc    |    792 |
|  3 | def    |    534 |
+----+--------+--------+

userID looks like this:

+----+--------+---------+
| id | userID |  name   |
+----+--------+---------+
|  1 | abc    | Angela  |
|  2 | def    | Gerard  |
|  3 | zxy    | Enrico  |
+----+--------+---------+

In results, the userID field is a lookup field; it stores userID.id but the combo box has userID.userID as its choices.

When I try to enter data into results by setting the userID combo box and entering a value for result, I get this error message:

You cannot add or change a record because a related record
is required in table `userID`.

This is strange, because I'm specifically selecting a value that's provided in the userID combo box.

Oddly, there are about 100 rows of data already in results with the same value for userID.

I thought this might be a database corruption issue, so i created a blank database and imported all the tables into it. But I still got the same error. What's going on here?

Upvotes: 3

Views: 9025

Answers (2)

brucemccampbell
brucemccampbell

Reputation: 41

If you are using a parameter query, make sure you have them in the same order as the table you are modifying and the query you have created. You might have one parameter inserting the conflicting data. Parameters are used in the order they are created...not the name of the parameter. I had the same problem and all I had to do was switch the order they were in so they matched the query. This is an old thread, so I hope this helps someone who is just now having this problem.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Both tables include a text field named LanID. You are using that field in this relationship, which enforces referential integrity:

Relationships Diagram

The problem you're facing is due to the Lookup field properties. This is the Row Source:

SELECT [LanID].ID, [LanID].LanID FROM LanID ORDER BY [LanID];

But the value which gets stored (the Bound Column property) is the first column from that SELECT statement, which is the Long Integer [LanID].ID. So that number will not satisfy the relationship, which requires results.LanID = [LanID].LanID.

You must change the relationship or change the Lookup properties so both reference the same field value.

But if it were me, I would just eliminate the Lookup on the grounds that simple operations (such as this) become unnecessarily confusing when Lookup fields are involved. Make results.LanID a plain numeric or text field. If you want some kind of user-friendly drop-down for data entry, build a form with a combo or list box.

For additional arguments against Lookup fields, see The Evils of Lookup Fields in Tables.

Upvotes: 1

Related Questions