Results 1 to 6 of 6
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Updating a Foreign Key

    I need advice or ideas on how to resolve a design issue that has come to light.



    I have the table
    tblSeabrookcompany
    SeabrookCompanyID - Primary Key
    Company - Text Field

    I have the related Table (Limited to relevant fields)
    tblGITClaims
    ClaimsGITID - Primary Key
    SeabrookCompanyID - Foreign Key

    So a relationship has been established between the Primary Key and Foreign Key.

    I now need to edit the Foreign Key in tblGITClaims.
    The FK is a number passed only through openArgs in an unrelated form.

    I'm scratching my head as to how to do this, any ideas?


    Regards

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use an update query, if you do not want to amend manually?
    Correct the entry when new record first, them amend for the correct value for those already in the table?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what do you mean by edit the key? To link it to a different parent? something else?

    you say a relationship is established - this does not happen automatically so you have created the relationship in the relationships window? If so, have you enforced referential integrity?

    which FK is passed? the original or the amended one?

    without having a clear description of your design, the nature of the change and 'how' you want to make the change (what are the forms doing for example) and why you want to do it, can't tell you how to accomplish it

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. youd have a master form, tCompany, and a subform tGitClaims
    as you enter claims data the FK is added automatically.

    or
    2. open the Claims form alone (as master)
    pick from a list of Companies (listbox/combo) this fills in the FK

    or
    3. like Welshgasman said, run an update query. (but you dont 'edit' keys)

  5. #5
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Quote Originally Posted by Welshgasman View Post
    Use an update query, if you do not want to amend manually?
    Correct the entry when new record first, them amend for the correct value for those already in the table?
    I will be looking into this as I do not want a user to amend the table directly.

    If it was just me I would just go to the table and change the Foreign Key in the related table to the one I want, but as I have users doing data entry, I will look at a user defined update query.

    Regards

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Steven

    You say "The FK is a number passed only through openArgs in an unrelated form."

    If the Form is unrelated how does the relationship work? You would normally have a Form based on :-

    tblSeabrookcompany
    SeabrookCompanyID - Primary Key

    This PK is usually passed to the related table via Open Args


    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form Updating 3 related tables losing Foreign Keys
    By DannyDont in forum Programming
    Replies: 30
    Last Post: 01-29-2021, 03:48 PM
  2. Replies: 13
    Last Post: 06-28-2019, 11:14 AM
  3. foreign key not updating data from form
    By dave100 in forum Forms
    Replies: 1
    Last Post: 08-07-2013, 10:31 PM
  4. Replies: 10
    Last Post: 05-08-2012, 09:17 AM
  5. Replies: 2
    Last Post: 04-19-2012, 11:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums