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
Code:
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
Code:
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.
Code:
Print_Error:
Error_Log.AddNew
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
Error_Log.Update
Stop
lngErrors = lngErrors + 1
Return
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.