insert query causing key violation

[Amanda Byrne]

I have a query that runs when the user clicks a button in a form, and it
returns a message that says it did not add a record to the table due to a key
violation.

The query is:
INSERT INTO AccountInterest ([Ref Type], [Household ID], [People ID],
[Interest ID] )
VALUES ('People', '1', '1', '24');

AccountInterest only has one other field, which is [AccountInterest ID]. It
is the primary key field and is set to autonumber.

What could be the hangup?

Thanks, Amanda

 

[Allen Browne]

Tell us about the 4 fields: [Ref Type], [Household ID], [People ID], and
[Interest ID].

Are any of these foreign key fields that relate to another table?
Have you created relationships with referential integrity?
Is it possible that one of them is being assigned a value that is not in the
lookup table?

What is the data type of these fields?
If any of them are numbers, drop the quotes around the expression in the
VALUES.

 

[Amanda Byrne]

[Ref Type] is a text field that specifies whether the interest is a household
interest (applies to the whole family) or an indivdual interest. No
relationships.

Household ID is the primary key in the Household table- each household has a
record
People ID is the primary key in the People table
Interest ID is the primary key in the Interest table

In the AccountInterest table, Household ID, People ID, and Interest ID are
all Long Integer fields. There is a one to many relationship for all three
(where the primary keys are the "one" and the AccountInterest table is the
"many"), all referentially enforced. There is also a one to many relationship
between the Household table and the People ID table.

- Amanda

Tell us about the 4 fields: [Ref Type], [Household ID], [People ID], and
[Interest ID].

Are any of these foreign key fields that relate to another table?
Have you created relationships with referential integrity?
Is it possible that one of them is being assigned a value that is not in the
lookup table?

What is the data type of these fields?
If any of them are numbers, drop the quotes around the expression in the
VALUES.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that runs when the user clicks a button in a form, and it
returns a message that says it did not add a record to the table due to a
key
violation.

The query is:
INSERT INTO AccountInterest ([Ref Type], [Household ID], [People ID],
[Interest ID] )
VALUES ('People', '1', '1', '24');

AccountInterest only has one other field, which is [AccountInterest ID].
It
is the primary key field and is set to autonumber.

What could be the hangup?

Thanks, Amanda

 

[Allen Browne]

Good, so you kept the quotes around 'People', and dropped them from around
each of the numbers.

It should now work, provided:
- the household table does have a record 1;
- the people table does have a record 1;
- the interest table does have a record 24.

If any one of those tables is missing the matching record, it would be a key
violation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[Ref Type] is a text field that specifies whether the interest is a
household
interest (applies to the whole family) or an indivdual interest. No
relationships.

Household ID is the primary key in the Household table- each household has
a
record
People ID is the primary key in the People table
Interest ID is the primary key in the Interest table

In the AccountInterest table, Household ID, People ID, and Interest ID are
all Long Integer fields. There is a one to many relationship for all
three
(where the primary keys are the "one" and the AccountInterest table is the
"many"), all referentially enforced. There is also a one to many
relationship
between the Household table and the People ID table.

- Amanda

Tell us about the 4 fields: [Ref Type], [Household ID], [People ID], and
[Interest ID].

Are any of these foreign key fields that relate to another table?
Have you created relationships with referential integrity?
Is it possible that one of them is being assigned a value that is not in
the
lookup table?

What is the data type of these fields?
If any of them are numbers, drop the quotes around the expression in the
VALUES.

I have a query that runs when the user clicks a button in a form, and it
returns a message that says it did not add a record to the table due to
a
key
violation.

The query is:
INSERT INTO AccountInterest ([Ref Type], [Household ID], [People ID],
[Interest ID] )
VALUES ('People', '1', '1', '24');

AccountInterest only has one other field, which is [AccountInterest
ID].
It
is the primary key field and is set to autonumber.

What could be the hangup?

Thanks, Amanda

 

[Amanda Byrne]

Ah, so simple. You are correct, because there is no interest ID of 24. The
query is actually used in some form code, and since the Me.[Interest.Interest
ID] returned a value, I didn't question whether or not it was the correct
value- but at least now I know where to look.

Thanks! Amanda

Good, so you kept the quotes around 'People', and dropped them from around
each of the numbers.

It should now work, provided:
- the household table does have a record 1;
- the people table does have a record 1;
- the interest table does have a record 24.

If any one of those tables is missing the matching record, it would be a key
violation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[Ref Type] is a text field that specifies whether the interest is a
household
interest (applies to the whole family) or an indivdual interest. No
relationships.

Household ID is the primary key in the Household table- each household has
a
record
People ID is the primary key in the People table
Interest ID is the primary key in the Interest table

In the AccountInterest table, Household ID, People ID, and Interest ID are
all Long Integer fields. There is a one to many relationship for all
three
(where the primary keys are the "one" and the AccountInterest table is the
"many"), all referentially enforced. There is also a one to many
relationship
between the Household table and the People ID table.

- Amanda

Tell us about the 4 fields: [Ref Type], [Household ID], [People ID], and
[Interest ID].

Are any of these foreign key fields that relate to another table?
Have you created relationships with referential integrity?
Is it possible that one of them is being assigned a value that is not in
the
lookup table?

What is the data type of these fields?
If any of them are numbers, drop the quotes around the expression in the
VALUES.


I have a query that runs when the user clicks a button in a form, and it
returns a message that says it did not add a record to the table due to
a
key
violation.

The query is:
INSERT INTO AccountInterest ([Ref Type], [Household ID], [People ID],
[Interest ID] )
VALUES ('People', '1', '1', '24');

AccountInterest only has one other field, which is [AccountInterest
ID].
It
is the primary key field and is set to autonumber.

What could be the hangup?

Thanks, Amanda