Relationships / Ref Integrity

[JeffChr]

I am using code to add a record to a table - CUSTOMERS. CUSTOMERS is related to table SALESREP by a (SALESREP) one to (CUSTOMERS) many, on field REPID. I can't figure how to indicate that although a relationship exists, there does not have to be a REPID in the CUSTOMER table. I get an error message that the record cannot be added to the CUSTOMER table because I am missing REPID. REPID is unknown at record creation time.

I do have referential integrity turned-on for this relationship. There has to be a way to do this, doesn't there?

 

[lupins46]

My approach would be to set up a Rep called 'Unassigned' or whatever and use that rep for the customers who do not have one. That would enable you to retain RI which on the whole is beneficial.

 

[JeffChr]

I gave that a thought. However, I am writing this for end-users who may unwittingly delete "Unassigned". If I manually turn-off RI, everything works. But I don't think RI can be turned-off/on using code.

 

[lupins46]

How are they going to delete 'unassigned'? If you mean from the rep table then they could only do that if there were no unassigned customers. But you could programmatically prevent that in the rep table maintenance form.

 

[JeffChr]

OK - that makes perfect sense. Guess that's the way to do it. Thanks for your help!

 

[ChewDinCompSci]

I'm not completely sure of your question but from what I understand... Is REPID the key in either table? And if so, could changing this may help.

MatChew
codename: vba4dumbE