Relationship Questions

[Guest]

I have a database with 3 tables that I am linking via relationships, but am
having a problem. The database is for tracking any customer complaints that
we receive, and also tracking the call back records that our Customer
Relations department does.

Table 1 -- Customers
I have customer Information such as name, address, etc. I also have a
CustomerID field, which is set as an AutoNumber field, and is the primary key.

Table 2 -- Complaints
The various data that we collect about a customers complaint.
ComplaintID is an autonumber field, and is the primary key.
CustomerID is a Number field.

Table 3 -- Call Data
This has 4 fields: ComplaintID, Call Date, Call Time, Call Notes
ComplaintID is a Number Field.

I have a One-To-Many Relationship set betweek Table 1 and Table 2, using
CustomerID. This works beautifully.
I was then attempting to build a One-To-Many Relationship between Table 2
and Table 3 using ComplaintID, but get an error every time I try to enter
data in Table 3. The error is: "You cannot add or change a record because a
related record is required in table 'Complaints'."

Any suggestions on how to resolve this problem?

 

[Allen Browne]

This problem occurs when Access assigns a zero to the ComplaintID field.

To fix it:
1. Open the CallData table in design view.

2. Select the ComplaintID field.

3. In the lower pane, remove the zero from the Default Value property.

While you are there, you might like to set the Required property of the
ComplaintID field to Yes. This will prevent storing a call that is not tied
to any complaint.

 

[Guest]

I went and did that and I am still receiving the error. It is not making a
whole lot of sense.

 

[Allen Browne]

I assume that Access is attempting to enforce the relationship:
Complaints.ComplaintID => [Call Data].ComplaintID

That means for some reason you are entering a value into [Call
Data].ComplaintID that does not match an entry in Complaints.ComplaintID.
You are now trying to track down that reason.

If Complaints.ComplaintID is an AutoNumber field (Long Integer size), then
[Call Data].ComplaintID must be a Number field of size Long Integer. I
assume that part is correct, since you seem to have a relation being
enforced.

Are you using a combo for selecting the ComplaintID? If so:
Does the number of columns match the Column Count?
Is the bound column the correct field?

 

[Guest]

No, am not using any type of a combo or lookup field.
I did find the problem. Prior to adding the ComplaintID field, I simply had
a link between a CustomerID field in the CallData table and Complaints table.
Even though I deleted the relationship, the Complaints table still had a
subdatasheet attached that was the old one, then when I was creating the
relationship, I guess you could say that it was getting confused.
I simply opened the Complaints table, went to Insert --> subdatasheet and
had to change the subdatasheet relationships. Now it is working as it should.

I assume that Access is attempting to enforce the relationship:
Complaints.ComplaintID => [Call Data].ComplaintID

That means for some reason you are entering a value into [Call
Data].ComplaintID that does not match an entry in Complaints.ComplaintID.
You are now trying to track down that reason.

If Complaints.ComplaintID is an AutoNumber field (Long Integer size), then
[Call Data].ComplaintID must be a Number field of size Long Integer. I
assume that part is correct, since you seem to have a relation being
enforced.

Are you using a combo for selecting the ComplaintID? If so:
Does the number of columns match the Column Count?
Is the bound column the correct field?

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

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

I went and did that and I am still receiving the error. It is not making a
whole lot of sense.

 

[Joseph Meehan]

I have a database with 3 tables that I am linking via relationships,
but am having a problem.

Sorry I don't have an answer to your problem, but I did find it
interesting that your message was followed by a SPAM " 55 great sex +
romance ebooks .. "