Form Updating 3 related tables losing Foreign Keys

[DannyDont]

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

[orange]

How about stepping back and, instead of telling us what tables and forms you have, tell us in plain English about your "business".
That is what are the Members and Donors? How do they relate? hat sort of things do they do or have done to them. Plain English, no jargon.

[DannyDont]

Ok I will try. I thought I was rather detailed. The business is a Breast Cancer Awareness organization. Folks, mostly women, can join and receive a newsletter(s) and participate as volunteers, as well as join different activities. At the end of each year, these Members need to register again for the new year (A Yearly Membership). They and others from the general public, and other organizations can also donate money to help support the activities of the organization. So as I attempted to explain, there are organization MEMBERS and organization DONORS. But since it is a yearly renewal, it made sense to keep the demographic information, Name, Address, Phone e-mail, etc. even when their Member status or Donor status was reset at the beginning of each year. Since this organization is a charity, there is the need for tax information each year. This is reset along with the membership status and donor status at the beginning of each year. How are we doing so far? If I may now go back to my earlier description ... I set up a table for the demographic information and called it tblPerson. I set up another table for someone that becomes a member and called it tblMember. Lastly, since someone could be a donor without becoming a member, I set up the third table for donors and called it tblDonor.

When I first developed the Forms, I had a form for Members which included data from tblPerson and tblMember and a second form for Donors which included data from the tblPerson and tblDonor. However my customer indicated that it would be nice to be able to add both Member information and Donor information at the same time as most members would also donate at the same time. I tried a couple different approaches but did not have the knowledge to do so. Then I thought if I had all of the fields represented in one form, I would be able to accomplish the task.

I could have set up the relationships as a one to many (jargon) where the tblPerson was the one and the tblMember and the tblDonor were the many and keep a record for every year. Based on my lack of experience, it seemed more straight forward to stay with the design that one person can be on member, or one person can be one donor, or one person could be both a member and a donor. This is perhaps jargon, but I didn't know how else to present my case, so to speak.

So how does this help explain my problem?

I can provide my table layouts if that would help. I just need to know what is needed.

Again, I thank you for you help,
DannyDont

Note: I see that the forum brought up several other topics that might relate. I will read through those .

[orange]

I am working on something else at the moment and will look at your detail info later.
As a first step you may find a data model or models relevant to your business at
Barry Williams' site.

Getting your tables and relationships set up and tested to ensure they support your "business" is critical to a successful database application.

This link to stump the model may be helpful.

Good luck.

[ssanfu]

Would you post your dB? Make a copy of the dB, delete all data, Compact and repair, then compress (Zip) and attach it to a post.

[kd2017]

If I understand correctly, since it's 1-to-1, a person's primary key is also a donor's and member's primary key?

Shot in the dark here: for 1-to-1 relationships it matters which order you click the tables when you define your relationships. Try deleting and recreating the relationships, drag FROM the person table TO the member/donor table when you do it. This is how access knows that member/donors are dependant on person.

[DannyDont]

I am working on something else at the moment and will look at your detail info later.
As a first step you may find a data model or models relevant to your business at
Barry Williams' site.

Getting your tables and relationships set up and tested to ensure they support your "business" is critical to a successful database application.

This link to stump the model may be helpful.

Good luck.

I will take a look. Thank you.

[DannyDont]

I can do that. Where do I post them?

Thanks for looking into this for me.

[DannyDont]

I was careful I believe when doing that. I went from tblPerson Primary Key to tblMember Foreign Key and from tblPerson Primary Key to tblDonor Foreign Key. Of course most of this is new to me but looking at information on the Internet, this seemed like to correct way to do it. I did go back and delete and recreate those links a second time just to be sure. What puzzles me is why it works just fine if I do the activity in the Query but fails when doing via a Form???

Thanks for you response. Let me know what else I can provide. One member asked me to post my DB which I will do either tonight or tomorrow afternoon.

[DannyDont]

I believe that I have what you asked for. Now I just need to figure out how to attach it to a post ... I see it is attached!

All for now,
DannyDont

[kd2017]

I can't duplicate your error. Can you give us step by step instructions to reproduce the error?

BTW you don't have your relationships defined in the relationship window.

[DannyDont]

Non-defined relationships - that is probably because I deleted the three tables and copied in the structure from the production version. I do have them defined. I would have found that soon enough. Thanks. Also, I did see the error duplicated in the Query last night.

So here is the scenario.
Using the Members/Donors option:
1. Create anew Person and Member
2. Create a new Person and a Donor
3. Next add a Donor to #1 - watch the key values on the bottom of the Donor portion of the screen - in this example the Key Value for the member will be changed to 0 (zero)
4. Add a Member to #2 - watch the key values again - here the Donor Key Value will be changed to 0 (zero).
Of course when testing this it worked. So let's try this:
1. Create a new Person Record with out a Member or Donor.
2. Go to some other record and then come back to the one just created.
3. Create a Donor and you should get the error.
I apologize for all of the "Stop" statements. I have them in trying to catch where the thing is going bad.

[orange]

@DannyDont,

You have no relationships between tables set up in your database. This is where/how you establish referential integrity that enforces certain rules/facts of your business in the database.




These are the "business rules" based on your info so far.

The business is a Breast Cancer Awareness organization.
This business is a charity
People can join(register) as members
Members pay an annual membership fee
Members receive a newsletter(s)
Members participate as volunteers
Members may participate in different activities.
Each year Members must renew (A Yearly Membership).
Members and the general public, including organizations, may donate money
Donors are not necessarily members
A Person can be a member or donor or both
Donations support the activities of the BCAK.
We need demographic information, Name, Address, Phone e-mail, etc.
Member status or Donor status is reset/confirmed/updated at the beginning of each year.
As a charity we need tax information each year.

You mention Activities, but there are no examples. A brief description of Activity could be helpful.
You also mention Volunteer participation.

I am not a user of Access macros so can not comment.

You have spent considerable time and effort on your form/user interface. Your development approach is what some would call form centric. There are some proponents for that, but I am not one. Traditional development would have a data model (tables and relationships) based on business rules/facts; test data and test scenarios measured against the model; refinement of the model and/or vetting activities; leading to final data model/blueprint for physical tables and relationships development.

To get some experience with table, relationships work through this tutorial from RogersAccessLibrary. It will take about 30-45 minutes, but you will learn a procedure that you can use with any database.

See the Database Planning and Design link in my signature for several related articles.

You may find the generic data models for Charities at Barry Williams site helpful.
- http://www.databaseanswers.org/data_...rity/index.htm
- http://www.databaseanswers.org/data_...ties/index.htm
- http://www.databaseanswers.org/data_...tins/index.htm

Good luck.

[kd2017]

Non-defined relationships - that is probably because I deleted the three tables and copied in the structure from the production version. I do have them defined. I would have found that soon enough. Thanks. Also, I did see the error duplicated in the Query last night.

So here is the scenario.
Using the Members/Donors option:
1. Create anew Person and Member
2. Create a new Person and a Donor
3. Next add a Donor to #1 - watch the key values on the bottom of the Donor portion of the screen - in this example the Key Value for the member will be changed to 0 (zero)
4. Add a Member to #2 - watch the key values again - here the Donor Key Value will be changed to 0 (zero).
Of course when testing this it worked. So let's try this:
1. Create a new Person Record with out a Member or Donor.
2. Go to some other record and then come back to the one just created.
3. Create a Donor and you should get the error.
I apologize for all of the "Stop" statements. I have them in trying to catch where the thing is going bad.

I still am not getting the error after following your steps. BUT maybe try this
1. make sure that your relationships are set up to enforce referential integrity
2. within the donor and member tables delete the default value for the PersonID field

When following your steps I did see some funny business with the ID fields reflected in the textboxes you mentioned, again it still didn't throw an error for me. But after deleting the default value and setting up the relationships the funny business stopped.

[DannyDont]

I will try this ASAP. Thanks. I will report back.