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

    Okay, that's an interesting idea. How do I write that VBA code?

  2. #17
    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 is the primary key of the other table?

  3. #18
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    The primary key of Table A is the autonumbered ID generated by Access. The Primary key of Table X is the ID number of the participant.

  4. #19
    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
    How would you search for the right record to update in Table X?

  5. #20
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    Can you just tell me why this isn't working? I got this far in the code and it looks like it runs fine (If I enter a new number in the school bds# field no errors occur and the form says "calculating," but when I look at Table B the field has not updated.

    Private Sub School_BDS___BeforeUpdate(Cancel As Integer)
    CurrentDb.Execute "UPDATE [Table A] INNER JOIN [Table B] ON [Table A].ID = [Table B].ID SET [Table B].[School BDS #] = [Table A].[School BDS #]"
    End Sub

  6. #21
    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 guess you do not care for the way I assist people since you did not answer my question. Sorry.

  7. #22
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    I just don't understand your question. I was hoping to make it simpler.

  8. #23
    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
    Is there more than one field in the two tables that they share in common?

  9. #24
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    Table A and Table B have entirely identical fields.

  10. #25
    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
    In that case you can create a query for the RecordSource of your Form A that joins the two tables and includes the Table X field we need so it is easy to update making the UPDATE query unnecessary. Have you tried using the query builder to make a query that joins the two tables in a 1:1 relationship and returns the [School BDS #] field from both table so we can change them? The user need only see one of them. The 2nd field would be change in the BeforeUpdate event of the form as has been discussed earlier.

  11. #26
    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
    If you do not understand my instructions or simply want clarification on what I said, please post back and let me know.

  12. #27
    Leelers is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2008
    Posts
    17
    Okay, I finally got this to work for anyone else who wants to know:

    Private Sub School_BDS___AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    CurrentDb.Execute "UPDATE [table A] INNER JOIN [table b] ON [table A].ID=[table b].ID SET [table b].[School BDS #] = [table a].[School BDS #];", dbFailOnError
    End Sub

  13. #28
    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
    Great! Are you ready to follow the link in my sig and mark this thread as Solved?

Page 2 of 2 FirstFirst 12
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