Form Updating 3 related tables losing Foreign Keys

[DannyDont]

I just wrote a big long message with screen captures. When I review my message the images were converted to their binary whatever. So now I am tired and frustrated. BUT that is my problem, not yours.



I have created a Type 2 Join without Referential Integrity. When I attempt to include reference Integrity I get an error indicating that are tblMember and/or tblDonor records without an associated tblPerson record. I looked though my database and did not see anything that fit that bill.

Perhaps Important: I created my tables by Importing data from an Excel spreadsheet. This spreadsheet contain ALL of the information. However, some records just contained the data that I put into the tblPerson Record, some contained that and what I put in the tblMember record, some of the input contained data for just the Donor information that I included in the tblDonor. I gave you a fresh version of the database without any records. Perhaps my logic when creating the data for the various tables was not correct. Here is what I did:

1. I removed all of the data that would be part of the Member table and that of the Donor table. I imported the remaining data into the tblPerson and let Access assign the Primary Key. There were about 200 records.
2. I removed all data that would be part of the Person table and Donor table. I left the Primary Key value generated by Access as the Foreign Key for this tblMember. Before i did the import, I removed any records that did not contain data that would be in the tblMember. There were about 175 records.
3. I did the same for the Donor as I did for the Member. There were about 50 Donor records. Some of these had a corresponding entry in the Member Table, some did not. ALL had a corresponding Person Record.
Then I created my 1-1 relationships from tblPerson_PersonID to tblMember_PersonID and from tblPerson_PersonID to tblDonor_PersonID.
At this point things seem to be working as expected.
I then put all data together in a query and built a Form from that query. (This was to fulfill a customer need). This is when things began to go "south". This error does not occur every time but does ONLY occur when the is a Person record that has a Member record and you try to add a Donor record or the Person record has a Donor record and you attempt to add the Member record.

Not sure what to do now. I did not seem to find the default value settings you talked about.

I really do appreciate your help. Thank you!!!

[DannyDont]

I just emptied my tables and I was able to make the join with referential integrity so perhaps we are onto my problem.

[DannyDont]

I need to look at how I created the tables i guess. Since I emptied my tables and built them from scratch, everything seems to work as advertised. I will do more testing. So perhaps the error lies in the import process.

[kd2017]

Okay. I used an INSERT query to add a record to the person table and force an ID of zero and I think I've managed to duplicate your problem. The issue does seem to be the default value I mentioned before. I deleted the default value for the PersonID column in both the donor and member tables and it seems to have fixed the error.



For some reason the default value property was messing with the ID field of existing records??? I have to admit I don't understand why it was happening.

[DannyDont]

Thank you. I think this might be a big part of the problem because that is what the key is being set to when it goes bad. I will give that a try on my test DB.

[DannyDont]

I think there is something wrong with my import process as I still get the problem. I am going to do some work and will be back but it might be a day or two.

[kd2017]

Try running a compact and repair. (Make a backup first)

[DannyDont]

Well after much work, I think I finally have it working. Seems like that Default of 0 for the Foreign Key appears whenever i create copy of the structure of the DB without Data. I exported the data in the tables that was already in the database, thinking that if the records were in the DB they should be valid. However, I found an error in both the Member and Donor tables where a record had 0 (zero) for it's foreign key and of course there was not one in the Person table. I changed the value to a legitimate record that was in the Person table, repeated the import. Next I went to the Form that was given me issue and tried several different ways to make it fail ... it did not. So I consider this "Case Closed". I will do more testing tomorrow but I can sleep easy tonight.

I believe in the end it was two things that caused the issue.
1. The default value for the Foreign Keys in both the Member Table and the Donor Table were set to zero and somehow this created a record in each table that had zero as its key.
2. I believe that my import form Excel was flawed. As I was testing with those original Excel files earlier today, there were import errors, That is what got me thinking about Exporting the current data and then importing it to a new version of the table.

In any case, I want to thank everyone that played a part in helping me. I was truly lost and you led the way. I believe there is some way to mark this Topic closed but I do not remember at the moment. So if someone could provide one more piece of information I could finish this up, or someone can close this for me if possible.

Again, thanks to all!!

DannyDont

[orange]

Great stuff. If you want to post your latest database, I'm sure some will review and comment.
The forum is always here if you need advice/comments/assistance..../

[orange]

Great stuff. If you want to post your latest database, I'm sure some will review and comment.
The forum is always here if you need advice/comments/assistance..../

Top of thread, Thread Tools.

[ssanfu]

I looked at your dB and saw several issues that should be addressed.

In the code, EVERY module should have two lines at the top

Option Compare Database
Option Explicit

IIRC, only one module had Option Explicit. After I added the Option Explicit to all modules, there were several errors - mostly undeclared variables.

There are look up FIELDS in some of the tables.

I don't think the tables tblMember, tblDonor and tblPerson should be joined in a 1-to-1-to-1 relationship.
This is how I would join the tables:



Then I modified frmPerson and added frmMember and frmDonor as subforms. I'm not real happy with the look of the form; I would have only a few fields in continuous form view and used the double click event to open a form to add/edit all the info for persons/members/donors.
EDIT: I added some forms. Generally, the forms I added end in 2. See attached db

This form name is frmPerson


There is a lack of consistency in some of the fields of the tables
For instance, in:
table tblPerson, the field "StatusP" is Text - Field size = 1
table tblMember, the field "StatusM" is Text - Field size = 1
table tblDonor, the field "StatusD" is Number - Long Integer.
Since all of the statuses are in the table tblStatusType, I would have all 3 field types as Number - Long Integer..


Anyway, that is my $0.02.......

[kd2017]

Steve,
One person can be many donors?

[ssanfu]

From Post #3

.... 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....

Maybe I misunderstood.
Can't one person donate many times.?
And can't a person be a Member for many years?

So as I understood it, if a person is or wants to be a member, they have to re-register every year.
And a person can donate 0 to many times.


In the original design, the tblPerson table PK field was linked to the Donor table PK field and the Donor table was linked to the Member table PK field (1-to-1-to-1). IMHO, this a wrong design.


What am I missing?

[kd2017]

Ah, I missed that. Maybe 'membership' and 'donation' tables instead of 'member' and 'donor'?

Even if maintaining the 1- to-1 relationship scheme I like your subform idea too.

[orange]

Further to Steve's comments
-Can an Organization be a Member OR only a Donor?

Agree with his points re Option Explicit and consistency.
Agree with kd2017 re the potential table changes to Membership and Donations.
eg. There is a Person
-who may have a Membership or
-who may make a Donation or
-who may do both.

I think the underlying structure could use some work. As mentioned earlier, working with an existing model and a number of test cases with some test data, some improvements in the model could be made and vetted.