[AccessD] DefaultIID Referential Integrity Puzzler

You can't maintain b-directional referential integrtity - you will always need to create one 
record before the other.

That sort of business rule needs to be enforced in the front end. My first reaction would be to 
prompt for the contact details in the before_update if it is a new record, store them in 
termporary variables and then create the contact record in code after the company record is 
saved.

-- 
Stuart

On 10 Apr 2014 at 2:09, Bill Benson wrote:

> This is related to a question Jon asked.
> 
> I just set up a database's relationships between Company and
> CompanyContact according to these rules... and found I could do it but
> of course couls not add any data!
> 
> 
> So, companycontacts is in a many to one with company through a
> field companycontact.fkcompanyid (many) to company.companyid (one)
> 
> But I also thought, suppose we want a defaultcontact at each company,
> who must be found in the companycontact table. So company is in a many
> to one relationship with companycontact through a field
> company.fkDefaultContactID (many) to companycontact.contactid (one).
> 
> Access let me put in these relationships but I could not add a contact
> because there was no companyid I could tie them to (company table was
> empty), and I could not add a company because I could not populate the
> default contact field, as there was nothing yet in the companycontact
> table.
> 
> This is a weird catch22.
> 
> I think the solution is that the first contact would be the
> defaultcontact, but then again, that person could leave and need to be
> superseded by a new default contact.
> 
> So, how can one maintain bi-directional referential integrity in this
> situation? -- AccessD mailing list AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
>