Solved

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

Posted on 2012-04-05
5
321 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
5 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now