3

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?

sigil
  • 9,370
  • 40
  • 119
  • 199
  • What is the row source of your combo box and its bound column? – Zaider Sep 13 '13 at 20:00
  • @Zaider: Row source is `SELECT [UserID].[ID], [UserID].[UserID] FROM UserID ORDER BY [UserID];` and the bound column is 1. – sigil Sep 13 '13 at 20:21
  • I can't replicate this problem. Can you upload a copy of your db to a file sharing site and give us a link? – HansUp Sep 13 '13 at 22:59
  • @HansUp, I have to leave now. I'll upload a sanitized version tomorrow. – sigil Sep 14 '13 at 00:19
  • @HansUp, I've shared it at [this link](https://www.dropbox.com/s/pedy101repnf952/xyz%20-%20Copy.accdb). Note that `UserID` is changed to `LanID` in this version, and I've removed the `LanID.Name` column. – sigil Sep 16 '13 at 21:35

2 Answers2

1

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • But see a rebuttal [Blog Response: Lookup fields in Access are evil?](http://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/) – Martin F Feb 07 '15 at 01:52
0

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.

brucemccampbell
  • 41
  • 8