Solved

MS Access form  Parent - Child Link fields problem

Posted on 2016-09-14
2
55 Views
Last Modified: 2016-09-15
I am working on database that somebody else created before. It is created in access 2003.I am trying to understand what is happening but some things are confusing.

 In this database I have Parent – Child (Main form – Sub Form) environment. Something similar to Order and line item situation.
Those two forms are linked with three fields.
Link1
Link2
Link3

They are bound forms.
Parent form frmMain is bound to tblParent and  sub form frmSubForm is bound to query Qry_Child.
Query Qry_Child is composed from two tables: tblParent and tblChild fields.

In query Qry_Child tables are connected with left join on those three fields that are link fields for the sub form and exist in both tables.
I want to point out something about those three linking fields between frmMain and frmSubMain.
Sub form frmSubFrom is based on Qry_Child and in Qry_Child those three fields are not from tblChild (even they exist there) but  they are from tblMain.
So my question is which link fields are in form Parent – Child link used here.
Are link fields from tblMain and tblChild  those filed used for  join in query or those from frmSubForm that is based on Qry_Child. Again I am pointing out that those three fields in Query are brought in from tblMain not from tblChild.

I am asking this as I have problem when I run this database on PC that has MS access 2003 and change value of Link2 field on frmMain it changes value of field Link2 in tblChild.
However if I run the same database on PC that has Access 2010 and I change value of link2 field on frmMain  I lost data on my sub form frmSubForm it is blank as no fields showing,  just all area where sub form was  is flat gray.
Only difference on Main form between these two database is that in 2010 database main form frmMain Link2 field is seating now on form tab Control that was not existed in 2003 version.

Not sure what is happening?
0
Comment
Question by:Taras
2 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41798741
Sometimes bugs are fixed from one version to the next and other times edits are tightened up.  Both can cause differences in execution.  Your problem is that the original form was incorrectly defined and an earlier version of Access was compensating for you.

1. The subform query will almost never need to join to the parent table.  So remove the parent table.
2. If you, because of some strange circumstance that I just cannot imagine, decide that you must keep the parent table in the subform query, DO NOT select the "key" fields from the parent table.  They must be selected from the child table.

The master/child links are used by Access to control populating the foreign key in the subform.  So in the case of something like tblOrder and tblOrderDetails.  The primary key of tblOrder is OrderID.  The primary key of tblOrderDetails is OrderDetailsID.  The foreign key to the order table in tblOrderDetails is OrderID.   The Master/child link field will be OrderID from the master and OrderID from the Child.  When you insert a row in the child table using the subform, it is the Master/child link definition that enables Access to populate the OrderID in the details table with the OrderID from the parent table.  Because you have chosen "OrderID" from the parent table in your subform query, Access cannot populate OrderID in the child table - you didn't include it.  I have no idea why an earlier version of Access allowed you to get away with this.  I would say THAT was the bug and the current version is working correctly.
0
 

Author Closing Comment

by:Taras
ID: 41800421
Thanks Pat.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bind Combobox 4 29
VBA to alter Table in MS Access .mbd (2000-2003) database 5 27
Getting Run-Time Error 13 - Type Mismatch 3 25
Search Form not Querying 2 10
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

914 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

20 Experts available now in Live!

Get 1:1 Help Now