Results 1 to 2 of 2

Single update updates 2 tables. Why?

  1. #1
    jsivanan is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019

    Single update updates 2 tables. Why?

    I'm not a newbie. I've been making a living coding in Access VBA over 30 years, but this has me stumped.
    I have a sub behind a form button that opens a table (tblEdit) and updates it. Confusingly a duplicate table tblEdit_Template is simultaneously updated. Both tables are linked to from an external database (to keep the data separate from the code). tblEdit_Template is used to initialize tblEdit at the beginning of the subroutine using

    DoCmd.CopyObject , "tblEdit", acTable, "tblEdit_Template"
    The table consists of an ID and a short string. This is the only reference to tblEdit_Template in the code and it's executed only once.

    Then tblEdit is opened with
    Dim Error_Log As DAO.Recordset
    Set Error_Log = db.OpenRecordset("tblEdit")
    As the sub progresses and analyzes some data, a condition worth logging is discovered and a relevant value is moved to a string called Msg and I do a Gosub to a "Print_Error" routine (below) which is meant to update tblEdit.

    strWeek = Trim(WeekLabel) + " Week"
    If Len(strWeek) < 16 Then strWeek = strWeek & Space(16 - Len(strWeek))
    If WeekLabel <> "" Then Msg = " " + strWeek + "    " + Msg
    Error_Log!ErrorDescr = Path + Msg
    lngErrors = lngErrors + 1
    At the point where the Stop (inserted to debug) occurs, when I check tblEdit, it is updated as expected, however tblEdit_Template is also updated with the same data!!!

    So the question is "Why?" and "How can I stop it from happening"? I even tried renaming the template tblTemplate_Edit, deleting and recreating the linked tables and relinking, and rebooting. I'm using Access 365 on Windows10.
    Thanks in advance for your help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Both tables are linked to from an external database
    DoCmd.CopyObject , "tblEdit", acTable, "tblEdit_Template"
    If tblEdit is a link (not a local table) and you make copy of that link named tblEdit_Template, you have just copied the link. These links are looking at same source. Edit one and edit will show in other.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Update Query that updates to minimum value
    By linvall in forum Queries
    Replies: 16
    Last Post: 09-18-2018, 10:01 AM
  2. how to update two tables using single form?
    By MykeRuiz in forum Access
    Replies: 4
    Last Post: 06-12-2016, 05:14 PM
  3. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  4. Replies: 8
    Last Post: 11-07-2013, 08:33 AM
  5. Replies: 10
    Last Post: 08-09-2012, 01:07 PM

Posting Permissions

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