Error for join key?

[chellebell1689]

Hello,
I have a member detail form and just starting today, it's been giving me an error (picture attached). I've checked to make sure both tables are assigning a primary key. I don't really know why I'm getting this nor can I understand what it's trying to tell me. This happens when I try to assign a family ID to a new member. This has worked just fine in the past (considering I added 450 members and never got this error). I can't think of anything I've done to the form to make it change. The only thing I've done since the last time it worked is add two drop down boxes for searching for a member (one by first name & other by last name).

I hope I put enough information in. I need this solved ASAP as I cannot add new members until this is fixed. Thank you in advance for the help!!

 

[AccessBlaster]

Just a guess: your foreign key is set to text and not "number"?

 

[chellebell1689]

Nope, checked the tables, all foreign keys are set to "Number". Thank you for the try!

 

[AccessBlaster]

Post a screen shot of the attempted relationships.

 

[pbaldy]

Are the new drop downs unbound? They should be.

 

[chellebell1689]

Yes they are unbound.

The member detail form is based mostly on the Members table, but needs the foreign key from the Family table, which requires a foreign key from the Deacon Table. When assigning the family to a new member, I know all the primary keys are created and ready to pass on as a foreign key.

Thank you so much for the help!

 

[chellebell1689]

Has anyone been able to figure this out? I did look around and found some that had the issue, but they said once they removed the attachment field from the form it worked again. I tried that and no such luck.

Desperately need this fixed, please!

 

[pbaldy]

To me the error sounds like the form is trying to save a child record before it has a matching parent record. You might try forcing a save of the parent record. Can you post the db here?

 

[chellebell1689]

Here's the db. There are some extra tables & forms because it pops up a warning that they're missing if I don't include them, and just in case one of them is causing the problem. I did leave a family ID in the family table, but there's no special information.

Sorry for the late reply. I only work on Tues & Thurs & last Thurs I was on a different task for the day. Thank you so much for the help!

**EDIT**
I forgot to mention. The form that I'm having the error with is the Member Details form. (the others are referenced in this form)

 

[pbaldy]

How does one recreate the error? The "find" combos don't show any records, nor does the form.

 

[gemma-the-husky]

strange errors like that often represent corruption. I would inspect the data for Chinese characters or something of that nature. Might be hard to find

 

[chellebell1689]

Oh sorry. Little scatter brained today.
So when creating a new member (or even updating an old member), if you try to assign a family ID you will receive the error. (Note: you will have to assign a SSClass (on sunday school tab) before you can save the member.)

You said to try saving the parent first, I tried that and still got the error.

 

[pbaldy]

I see the error, but if I take all the other tables out of the record source of the main form, I don't get it anymore. Why are they in there? I assume the problem is with another relationship. I would use subforms for related data.

I suppose by now somebody has already mentioned the lack of normalization in the design?

 

[chellebell1689]

Yes it has been mentioned, but I don't know how to improve it further. It's a huge-giant improvement on the original one I was working on, but now when I'm told I need more work, I'm just directed to the tutorials (which I do appreciate, but it's not getting me that last bit that I'm apparently missing).

As for the other tables, they're in there so that when a field needs to reference something (like the SS Class) it will show the actual data and not the ID from the table. It's just to make it easier to read. I'll play around and remove a table at a time to see where my error disappears. Thank you.

**EDIT**
So I tried what you said (removing the tables), I removed one at a time and found that I still get the error, until I remove the Family table which is the one that controls the drop down box so I can assign a family to the new member.

 

[pbaldy]

I'd take the table out of the record source and use a combo to display, like you did for the Family ID combo at the top. The family table doesn't have to be part of the form's record source for that to work.

 

[chellebell1689]

That makes sense. But I did find that it was pulling the Deacon ID from the family table and pulling the Deacon's name from the Deacon table. How do I get this to continue to show with out having those two tables?

Thank you so much for your help!

 

[pbaldy]

It appears deacon is associated with family, not member? I'd take the combo out, add to the family combo rowsource and use this to display the deacon once a family is chosen:

http://www.baldyweb.com/Autofill.htm

 

[chellebell1689]

That sounds like a good idea, but the family table does not record the deacon's name, just their number. The Deacon's are in their own table so that I can generate a report for them that will list all the families (and family members) that are assigned to them.

**EDIT**
Also, I just noticed this, I have a field, "InDirectory", that is pulled from the family table (does the family want to be in the Directory?) and with out the family table in the source query I can no longer track this.

Why would this start happening all of a sudden? It was working perfectly fine! I added 450+ members with no problems at all!

thank you again for your help!

 

[pbaldy]

I meant changing the family ID rowsource to:

SELECT Family.[FamilyID#], Family.FamilyID, Deacon.DeaconName
FROM Deacon INNER JOIN Family ON Deacon.DeaconID = Family.DeaconID
ORDER BY Family.[FamilyID];

and using the method above to display the deacon name after selecting a family.

 

[chellebell1689]

So since I have "InDirectory" field pulling from the Family table I wanted to try something...I removed the "DeaconID" from the form query source and then replaced the Family ID row source with your SQL above and the error is gone. So my conclusion is that it was having a problem trying to save the Fam ID & Fam Deacon at the same time.

Thank you for your help! Going to try your method to display who the Deacon is. Thank you again! (leaving this open till I get the deacon part to work as well.)