I am a struggling Access Database creator. I have a database containing three tables. The primary table is the PERSON table. There are two tables with a one to one relationship with PERSON. These are MEMBER and DONOR. It is possible to have a PERSON with no additional records in MEMBER or DONOR. It is possible to have PERSON/MEMBER and no DONOR. It is possible to have PERSON/DONOR with no MEMBER. It is possible to have PERSON/MEMBER/DONOR.
I have a form based on a query that brings the data from all three tables together. This is done because many times a real PERSON will become a MEMBER and a DONOR at the same time. The problem that I am having is that based on certain conditions that I can't yet figure out, the Foreign Key value for MEMBER or DONOR will get changed to 0 (zero), What is changed is the Foreign Key of a table record that exists gets replaced by the 0 (zero). This of course causes an error due to possible duplication of the Key Value. I have used many of the interrupts at the FORM level and display all three keys, Primary Key from PERSON, and Foreign Key from MEMBER and DONOR. I do have the Key Values displayed on the form. If I work on a record (from the query) which contains all three tables that does not have either a MEMBER or a DONOR, I get this error. I cannot figure out how it is happening. My latest test shows the Key changing when I go from one field to another (in the same table's fields) on the Form. What testing I have done within the query, things seem to work as I would expect. The NEW RECORD in either the MEMBER or DONOR tables picks up the key value from the PERSON Primary Key. But for some reason things are not happening the same way in the FORM???
I am not versed in ACCESS so my table structure might be in question, but to me it seemed to be logical.
At this point I have told my story but I have no idea what to do next. Have I given enough information? What more do you need to help me?
I thank you in advance for your help,
DannyDont