Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic Edit Table B Based On Change In Table A (2000)

    In my opinion, I screwed up when designing a database and I can't find on my own a solution to the screw up. It goes like this:

    My user can make full edits on a Table called "tblAgent_Agency", that has a Primary Key called "SubjectID". When doing this, the "system" is accessing just that Table and everything goes as planned. The problem comes up when my user has records in the Table called "tblActions", that in other processes joins "tblAgent_Agency" by way of "SubjectID". The join looks and displays only records in "tblActions" that have matching records in "tblAgent_Agency". I forgot to provide for an update in "tblActions" based on Edits to "tblAgent_Agency" and I'm left with orphan records that can't be updated by the user. As always, any guidance on how to painlessly achieve this is appreciated. Thanks in advance for any assistance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic Edit Table B Based On Change In Table A (2000)

    Unless the number of records is prohibitive, I'd fill in the missing SubjectID values manually.
    Once you have done that, create a relationship between tblAgent_Agency and tblActions on SubjectID and tick the check boxes "Enforce referential integrity" and "Cascade update related records" in the dialog.
    It won't be possible any more to create orphan records.
    The best way to make Access fill in the appropriate SubjectID automatically is to use a main form and subform.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Edit Table B Based On Change In Table A (2000)

    Hey, Hans. Thanks again! Will do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •