1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access master child link on calculated field workaround

Discussion in 'Business Applications' started by rawnrg, Nov 17, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. rawnrg

    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.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  3. rawnrg

    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
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  5. rawnrg

    rawnrg Thread Starter

    Joined:
    Nov 17, 2011
    Messages:
    4
    You're an angel- thank and bless you.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027279

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice