Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-04-05
5
Medium Priority
?
378 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 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 1200 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 400 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 1200 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

Technology Partners: 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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

572 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