• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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
0
ckwillGWU
Asked:
ckwillGWU
4 Solutions
 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now