Persistent Relationship

[Sprinks]

After making some changes to a database, we were unable to add new Company
records, getting a 3201 error message "Unable to add record because related
record is required in table Company."

Opening the Relationships window, there was a relationship set between
Company and a 2nd instance of Company (Company_1) on the CompanyID field. I
instead wanted a relationship between the RepID field and
Company_1.CompanyID. After deleting the unwanted relationship and adding the
new one, I'm still getting the same error. I selected Show All from the
window to ensure all relationships are displayed.

Does anyone know what's going on?

Sprinks

 

[Jerry Porter]

If RepID represents the ID of a person, it doesn't seem right to link
that to a company id. And in general, it would rare to have a
relationship between two fields in the same table.

Do you have a table of Reps where Rep ID is the primary key? Maybe
that's where this link should go.

You might need to explain what these fields mean, and what you want
your relationship to accomplish.

Jerry

 

[Sprinks]

Rather than have tables Reps, Mfrs, Contractors, etc., I have a single table,
Company, with a CompanyType field. RepID is a recursive link to the same
table. It only has a value when the CompanyType is a Manufacturer, and
represents the CompanyID of the associated representative.

Sprinks

 

[Jeff Boyce]

"How" did you "delete the unwanted relationship"?

Did you highlight the join/relationship line and press <Delete>?

If you didn't do this, when you press <Show ALL>, you see it "back" again.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Porter]

I assume CompanyID is the primary key.

First thing I'd check is that relationship was created in the right
direction. In the Relationships window, you should see the 1 by
CompanyID, and the infinity sign by RepID. In the "Edit Relationships"
window, Company/RepID should be the "Related Table".