Access master child link on calculated field workaround

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rawnrg

Thread Starter
Joined
Nov 17, 2011
Messages
4
Greetings ya'll
I have a tabbed main form to multiple subforms situation (Access 2010 Windows 7).
I need a unique ID to link master/child form and I understand one shouldn't link the auto-number primary key. Main table/form is client general contact info and the one to one related subforms are business info, emergency contact info, verterans info, and volunteer info. I need all these related one to one and linked by individual record so that entry is by each person's record. I am struggling with the unique ID to use to link everything. Can't use last name since some people share same last name. So I created a calculated field that combines first name, last name, and DOB into one field- a unique ID. BUT, you can't link calculated fields.
Can anyone think of a work around for this??
Would it work to put calcualtion on form field instead?
What about a query that takes the data from the calculated field and makes a new uncalculated columm I can link?
VB code workaround?
I think the answer to this may help a lot a novice DB developers.
Thank you in advance for working on this with me. It is for a non profit that provides equine therapy.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
rawnrg , welcome tot he forum.
I am at a bit of loss to understand your problem for a couple of reasons.
First Autonumber Key fields are for linking tables, but under the correct conditions.
Second I do not understand you havind One to One tables. If you have one to one tables the data might just as well be in One main table.
Sub tables should be for One to Many or Many to Many relationships.

In your case, if you must have one to one tables you use the Key field from the main table and have a foreign key in the other tables to hold that data.
 

rawnrg

Thread Starter
Joined
Nov 17, 2011
Messages
4
Thank you, this is a great service you are providing.
I must have misunderstood this answer from another string: "
If the data sources of the Subforms are in different Tables then you should not be using the Autonumber to Link them to the Main Form. Each Subtable should have a Maintable ID field to create thr correct Relationship between the tables. That is the field that should be used to Link the SubForms to the Mainform."
And I have verterans data, business data, general contact data, and emergency contact data in seperate tables because I read about normalizing and thought that was what it meant. But it sure would be easier to put it all in one table but seperate forms because so much data entry to view.
If I left in seperate tables and I link by main table auto number primary key (ClientID) the process would be add Client ID to all other tables to link; add text box sourced to this Client ID in tables, create relationship from all ClientIDs to main table ClientID, and the ClientID will automatically populate the subform clientID text boxes and the ClientID table columns?
In great appreciation for your patience with me
 

OBP

Joined
Mar 8, 2005
Messages
19,896
You correctly say "If I left in seperate tables and I link by main table auto number primary key (ClientID) the process would be add Client ID to all other tables to link; add text box sourced to this Client ID in tables, create relationship from all ClientIDs to main table ClientID, and the ClientID will automatically populate the subform clientID text boxes and the ClientID table columns?
The ClientID in the Sub tables should be set to Number type Long Integer (Long).

Are you certain that the tables will be One to One, I would set them up as One to many using the method that you have described, to do so set Referential Integrity to Yes.

You may want the table's data to go the other way, ie. have the KeyID field in the Subtable stored as a foreign key in the main table. This is used for things like Town or City names, department names etc.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Let me know if you have any more problems with it.
I can set up relationships in Access 2000-2003 that will work ok in Access 2007-2010.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top