MS Access Forms working 1-to-1 Relationships with Multiple Sub-Forms

I'm working on a Master Access Databases with A General Form and several Subforms. My issue is that I want the Access Information, Contact Information, and Financial Information Subforms to the General Information Form to have 1-to-1 relationhips. However, when I try this by with or without enforcing referential integrity, the information on the subform is showing up on all the records and the option to add more records is there (when it shouldn't be).

Can someone please tell me what I am doing wrong.

Also, I get an error, when I try to update the Financial tab. My relationship and error pics are attached.

I'm looking forwards to your responses, I've been trying to figure out on my own for over a week :(
Accounts-DB-Relationships1.jpg
02-Error-GeneralInfoTab.jpg
ckwillGWUAsked:
Who is Participating?
 
mbizupCommented:
The fields you are joining on are okay (the General_Account_ID relates the tables), but I think your subform tables should look like this as an example:

Access_Information_Table
___________
AccessInfoID
General_Account_ID
Username

etc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I want the Access Information, Contact Information, and Financial Information Subforms to the General Information Form to have 1-to-1 relationhips. "

My I ask why?  That is not the general intent of a Form/Subform combination ... as opposed to 1:Many

mx
0
 
mbizupCommented:
Your subform tables should each have ther OWN primary keys.

General_Account_ID should only be listed as a PK in your General Info table (Main).  It should be a foreign key (without the key symbol)  in the rest of the tables
0
 
peter57rCommented:
Access will assume a 1-1 relationship if you are linking the tables on a primary key fields.
You will not be able to add a duplicate record into either table (duplicate based on primary key) so the fact that the new record button is still active does not mean you can add more records.

The form/ subform link is not constrained by the relationship - it is a quite independent link and the type of relationship - or indeed whether or not a relationship exists - is not relevant to the mainform/subform design.
0
 
ckwillGWUAuthor Commented:
Thanks All, All comments were combined in my solution. I moved some information to the main table that I had issues with... I added primary keys to the subforms and then updated the subform tables with the General_Account_ID as a foreign key. I created relationships with the primary and foreign keys between the tables (not sure if exactly correct, but seems to be working so far). In the subforms, it will continue to allow the 1:Many relationships, but that is not a major factor -- I'll just remember to add only one record and multiple if needed in the future (for flexibility).

- Thanks again, Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.