“您不能添加或更改记录，因为需要相关记录"，但是存在相关记录吗? [英] "You cannot add or change a record because a related record is required", but related record exists?

问题描述

我有两个相关的表results和userID.

results看起来像这样:

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

userID看起来像这样:

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

在results中，userID字段是一个查找字段；它存储userID.id，但是组合框具有userID.userID作为选择.

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

当我尝试通过设置userID组合框并输入结果值将数据输入到results中时，出现以下错误消息:

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`.

这很奇怪，因为我专门选择了userID组合框中提供的值.

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

奇怪的是，results中已经有大约100行数据，而userID的值相同.

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?

推荐答案

两个表都包含一个名为LanID的文本字段.您正在此关系中使用该字段，这将强制实现引用完整性:

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

您面临的问题归因于查找"字段属性.这是行来源:

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]; 

但是要存储的值(绑定列"属性)是该SELECT语句中的 first 列，即长整数[LanID].ID.因此该数字将不满足该关系，因此需要results.LanID = [LanID].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.

您必须更改关系或更改查找"属性，以便两者都引用 same 字段值.

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

但是，如果是我，我只会消除Lookup，因为当涉及Lookup字段时，简单的操作(例如此操作)会不必要地造成混淆.使results.LanID为纯数字或文本字段.如果您希望使用某种用户友好的下拉菜单来输入数据，请使用组合框或列表框来构建表单.

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.

这篇关于“您不能添加或更改记录，因为需要相关记录"，但是存在相关记录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！