Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17

    Auto-updating fields in linked tables?


    I have a large main table in my database, as well as several small tables which have been generated from the original table based on wave of data collection. (for example, main Table A contains names and addresses of study participants for rounds 1-5, while tables B, C, D, E, and F contain names and addresses of study participants for each individual wave). What I want is when someone enters data in table A, for the identical fields in tables B-F to automatically update without having to run an update query. Is this possible?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would think that rather that a different table you would simply have another field in Table A. A truly normalized RDBMS only has the actual data in one table. You can reference it with ForeignKeys in other tables but the actual data is only in one table.

  3. #3
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    In this case, I originally did have one table but chose to build a new table because we are starting a new (but somewhat related) study. So I wanted to build forms and queries for this new study, but sometimes the new table for the new study needs to reference information from the old study.

    Please tell me how it is possible to trigger updates from one table to another automatically, without running an update query.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can do it from a form or in code but there are no triggers in Access tables.

  5. #5
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    Can you tell me how to do that from a form? Example:

    Form A is linked to a query based on Table A and contains a field for school number. Form B is linked to Query X (which is a combination of Table A and Table X) and also contains the field for school number (the same from Table A). When I update school number in Form A, I want it to also update in Form B.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    And would Form A run with Query X as a RecordSource? I believe you wanted to update *table X* when *table A* is changed right? You said Form.

  7. #7
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    Ok. This is hard to describe, sorry. Let me try again.

    I have Main Table A. Because of duplicitous records from Table A from multiple waves of data, I have also created Table B, which is every record from the last wave of data in Table A (this allowed me to create a true primary key based on our ID numbers).

    So, a simplified Table A is:

    ID Wave Name
    1 1 Jay
    1 2 Jay
    1 3 Jay
    2 1 Ali
    2 2 Ali
    2 3 Ali


    And Table B is like this:

    ID Wave Name
    1 2 Jay
    2 2 Ali

    I combined Table B with Table X in a query. I have a form that is based on a query of Table B linked with Table X (Form XB). I have another form that is based on a query from Table A (Form A). When someone enters data in Form A, I want the field that is shared between Table A and Table B to update so that the data appears in Form XB. Basically, I need a way to link the fields in Table A and Table B.

    does that help?

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What fields are shared and need to be kept in sync?

  9. #9
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    It is a field of school names/numbers. Table A has this field, and when Table B was created from Table A (via a make table query), Table B also received this field. This field is in Form A and is also in Form XB.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You do understand that forms are just windows into tables right? They do *not* contain data. Tables contain data.

  11. #11
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    I obviously understand this. I'm just trying to explain my set up. Can you answer my question??? Is there a way entries made into the school name field for Table A to auto-update the same field in Table B? Can I use macros for this? Or Events? How would I do that?

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would use the code in the BeforeUpdate event of Form A. Is Form XB allowed to modify this same field?

  13. #13
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    I locked the field in Form XB because I thought it might be easier if only one table was receiving the updates. Do you think I should unlock it?

    Can you link me to something that explains how the BeforeUpdate event works?

  14. #14
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    Can anyone help me write VBA code for this? I am not familiar with VBA and I can't seem to figure out which functions to use after
    Private Sub School_BDS___BeforeUpdate(Cancel As Integer)

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by Leelers View Post
    I locked the field in Form XB because I thought it might be easier if only one table was receiving the updates. Do you think I should unlock it? NOPE!

    Can you link me to something that explains how the BeforeUpdate event works? How about me?
    If you search for EVENTS in Access help there is a good explaination of when and how each event works. The BeforeUpdate event of the form *only* occurs when when the current record is dirty and being saved. It is the last form event just before the record is saved and can be Cancelled if you want. In your case you want to compare the particular control.Value to the control.OldValue. If they are different then that field has been changed and you will need to update the other table. I would probably use and UPDATE SQL statement and CurrentDB.Execute. If I knew more about your system there might be a way do do it with a Join.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. auto update of two linked tables
    By colenzo in forum Access
    Replies: 4
    Last Post: 07-20-2009, 09:08 AM
  2. Auto updating a total
    By Dreamcatcher in forum Forms
    Replies: 0
    Last Post: 06-19-2009, 02:18 AM
  3. help with linked tables
    By davidoakley in forum Access
    Replies: 9
    Last Post: 06-17-2009, 05:23 PM
  4. Updating two tables using SQL Insert Into
    By glazzaro in forum Programming
    Replies: 0
    Last Post: 05-02-2008, 10:52 PM
  5. Replies: 2
    Last Post: 04-17-2006, 08:13 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