Enforcing Data Accuracy from FK

[Minerva]

I have a DB with 3 tables for a repair shop: Customers, Jobs, Parts.



Customers:
PK -> Customer ID

Parts:
PK -> Part ID
FK -> Customer ID (see which customer owns the part)

Jobs:
PK -> Job #
FK -> Customer ID, attached to Customers table (see which customer the jobs is for)
FK -> Part ID (see which part the repair job is for)

I enforced referential integrity with cascading updates on all the relationships. However, as long as A part number exists in the Parts table, the customer ID in the Jobs table doesn't have to correlate to the customer ID in the Parts table.

So for example, John Smith may check in Part # 2, but in my database I can enter part # 1 and because part # 1 exists in the part table, this is accepted. How do I get Access to deny my entry if John Smith is not the customer for part #1 in the Parts table

[RuralGuy]

Start by turning off Cascading Updates and Deletes. They are not helping you in this case. Then describe the form you use to "check in" a part. Is it a MainForm/SubForm arrangement?

[Minerva]

Yes I am checking it in via a basic form.

The form has several text boxes, with a combo box for the Customer ID and Part ID that populates via a select query.

Realistically this will not happen from the end-user because the query will only populate accurate part data. It would only occur from someone manually typing in an entry in the table, but I still wanted to enforce it if possible. I know how to do this in Oracle just not Access.

[RuralGuy]

I believe you will have to enforce this validation with code. I do not see how Referential Integrity will help you with this special condition.