Solved

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

Posted on 2012-04-05
5
364 Views
Last Modified: 2012-04-05
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
Comment
Question by:ckwillGWU
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 37812473
"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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 300 total points
ID: 37812489
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 37812510
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
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 37812525
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
 

Author Comment

by:ckwillGWU
ID: 37813841
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question