I am attempting to utilize an idea I have found within an Access template listed as "Asset Tracking." One of the forms, Asset Details, has a comment tab with two fields, New Comments (which is associated with a comments field in the sourced table), and Comments History, which features the following code in the Control Source:
=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([txtID],0))
The code allows one to enter information into the comment field that then updates the comment history with that comment once the form is saved and closed. This is based on the append only option being set to "Yes" under the Long Text format. The result would look something like this:
[Version: 12/18/2019 5:00:22 PM ] Jonathan's Law
[Version: 12/18/2019 5:14:13 PM ] Complete Last Interview
[Version: 12/20/2019 9:35:52 AM ] Hello Paul
[Version: 12/20/2019 10:00:31 AM ]
[Version: 12/20/2019 11:42:54 AM ] And then she got fired
[Version: 12/20/2019 11:44:07 AM ] And never rehired.
I have a database that I am initially developing for my agency for incident management (four tables: Investigations, Investigations - Target(s), Investigations - Victim(s), Investigations - Target/Victim Joiner. See this previous question for more details), and I saw that this code would be handy in identifying when an allegation changes (i.e., Neglect to Mistreatment) or to provide a comment box with comment history of my own within the forms developed. I attempted the code in my form and created a text box with the following code(s) in the Control Source:
=ColumnHistory("[Investigations - Target/Victim Joiner]","Offenses","[TargetID]=" & Nz([TargetID],0))
OR
=ColumnHistory([RecordSource],"Offenses","[TargetID]=" & Nz([TargetID],0))
Originally I tried to have either of these on the main form but I realized that the main form does not directly source the correct tables needed (I was getting a #Name? error and could not remedy it), but one of the subforms within the form did. I attempted the above codes in a text box within my allegationsubform and now receive a #Error code. I am really not sure how to proceed; I am looking for a way essentially to create an audit trail of changes to one's offenses (and eventually outcomes) as they are changed. I am wondering if the many to many relationship between victims and targets based on my arrangement is also creating issues (the TargetID and VictimID on the junction table are both PK's as part of the many to many); in which case, I am curious if something like the following code (it didn't work for me) would be on the right path:
=ColumnHistory([RecordSource],"Offenses","[TargetID]=" & Nz([TargetID],0) & "[VictimID]=" & Nz([VictimID],0))
Any help regarding this would be much appreciated; my entire Friday has gone into trying to identify an article that clearly explains where I am going wrong and none so far have helped me make the connection. All errors I have seen others correct are either the result of the code miraculously working without explanation of what was changed, or a minor grammatical error was present (i.e., missing quotes) in the code.
Sources of Research prior to asking question:
How do I display a memo field to a Form in Access
https://access-programmers.co.uk/forums/showthread.php?t=293527
https://learn.microsoft.com/en-us/office/vba/api/Access.Application.ColumnHistory
Need an " AND " in the criteria:
=ColumnHistory("YourTableName","Offenses","[TargetID]=" & Nz([TargetID],0) & " AND [VictimID]=" & Nz([VictimID],0))