Using ColumnHistory Expression in an Form

[StuartG]

Hello Again all,

I am attempting to use the below expression in my access field:

=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

This was an example taken from a microsoft Template, "Customer Service" however I am attempting to get this to work in DB and it wont.

I attach a picture of what it is i am trying to achieve.

I have the following field in by TB table.

TBL Name is SalesData
Column = AuditComments

Any help would be greatly appreciated.

 

[MajP]

That looks correct. make sure the memo field being referenced is set to AppendOnly. It will error if not.

 

[MajP]

Column = AuditComments

Not sure if typo but you have "comments" in the call.

 

[StuartG]

That looks correct. make sure the memo field being referenced is set to AppendOnly. It will error if not.

Does this mean I have to have a seperate table that is an "append" table?

 

[MajP]

No the field in the table design view has a property
"Append Only" which is yes or no.

 

[MajP]

https://docs.microsoft.com/en-us/office/vba/api/access.application.columnhistory

 

[MajP]

"Append Only" seems like a stupid name. In that field you can still add, edit, delete. Should have been called "Save History". If you have "append only" set to yes and then select No you will get a warning that all history for that field will be lost.

 

[StuartG]

"Append Only" seems like a stupid name. In that field you can still add, edit, delete. Should have been called "Save History". If you have "append only" set to yes and then select No you will get a warning that all history for that field will be lost.

Agreed, is a rather silly name.
So i built that in as advised but i still get an error?
I referenced it correctly.

Followed a guide from:

http://www.fmsinc.com/MicrosoftAccess/2007/ColumnHistory/Index.asp

 

[MajP]

You keep changing your names. Now you have "Notes"

=ColumnHistory("SalesData","AuditComments","ID = " & Nz([ID],0))

To debug it first you may want to make a public function and pass in a valid ID. Here was my test

Public Function GetHistory(ID As Long) As String
   Debug.Print ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  Debug.Print "History" & GetHistory
End Function

 

[StuartG]

You keep changing your names. Now you have "Notes"

=ColumnHistory("SalesData","AuditComments","ID = " & Nz([ID],0))

To debug it first you may want to make a public function and pass in a valid ID. Here was my test

Public Function GetHistory(ID As Long) As String
   Debug.Print ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  Debug.Print "History" & GetHistory
End Function

I got that working.. Yes!

The next question how do you stop it repeating the last entered text, and also not put a blank time stamp in. (Attached pictures)

 

[MajP]

In order to do this I wrapped the function in a UDF and then cleaned up the empty and last entry. I know of no way to find where this information is really stored. May be in a system table.

Public Function GetHistory(ID As Long) As String
  GetHistory = ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  GetHistory = CleanEmpties(GetHistory)
  GetHistory = RemoveLastEntry(GetHistory)
  'Debug.Print "hist" & GetHistory
End Function
Public Function CleanEmpties(ByVal strHist As String) As String
  Dim aHist() As String
  Dim i As Integer
  Dim tempstr As String
  aHist = Split(strHist, "[")
  For i = 0 To UBound(aHist)
    'Debug.Print aHist(i)
    tempstr = Replace(aHist(i), vbCrLf, "")
    tempstr = Trim(tempstr)
    If Right(tempstr, 1) = "]" Then
      'Debug.Print "clean " & aHist(i)
     CleanEmpties = Replace(strHist, "[" & aHist(i), "")
    End If
  Next i
End Function

Public Function RemoveLastEntry(ByVal strHist As String) As String
  Dim aHist() As String
  Dim tempstr As String
  If strHist <> "" Then
    aHist = Split(strHist, "[")
    tempstr = aHist(UBound(aHist))
    tempstr = "[" & Trim(tempstr)
    Debug.Print tempstr
    RemoveLastEntry = Replace(strHist, tempstr, "")
  End If
 End Function
Private Sub Form_Current()
  Me.Recalc
End Sub

 

[StuartG]

In order to do this I wrapped the function in a UDF and then cleaned up the empty and last entry. I know of no way to find where this information is really stored. May be in a system table.

Public Function GetHistory(ID As Long) As String
  GetHistory = ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  GetHistory = CleanEmpties(GetHistory)
  GetHistory = RemoveLastEntry(GetHistory)
  'Debug.Print "hist" & GetHistory
End Function
Public Function CleanEmpties(ByVal strHist As String) As String
  Dim aHist() As String
  Dim i As Integer
  Dim tempstr As String
  aHist = Split(strHist, "[")
  For i = 0 To UBound(aHist)
    'Debug.Print aHist(i)
    tempstr = Replace(aHist(i), vbCrLf, "")
    tempstr = Trim(tempstr)
    If Right(tempstr, 1) = "]" Then
      'Debug.Print "clean " & aHist(i)
     CleanEmpties = Replace(strHist, "[" & aHist(i), "")
    End If
  Next i
End Function

Public Function RemoveLastEntry(ByVal strHist As String) As String
  Dim aHist() As String
  Dim tempstr As String
  If strHist <> "" Then
    aHist = Split(strHist, "[")
    tempstr = aHist(UBound(aHist))
    tempstr = "[" & Trim(tempstr)
    Debug.Print tempstr
    RemoveLastEntry = Replace(strHist, tempstr, "")
  End If
 End Function
Private Sub Form_Current()
  Me.Recalc
End Sub

Which Section would you build this VBA into?

 

[isladogs]

I know of no way to find where this information is really stored. May be in a system table.

I found out the answer to that when I was researching system tables a few months ago
The memo field column history is stored in a deep hidden table with a very long name similar to:
"f_185CEC69CB3440E0BF69D8330221AE41_VersionHistory_F5F8918F-0A3F-"

For anyone who is interested the attached zip file contains a PDF explaining how this property works together with the example database referenced in the PDF.

In order to view (and edit) the contents of this table, I had to do a bit of trickery.
If anyone wants to know how I did this, please send me an email

 

[MajP]

In order to view (and edit) the contents of this table, I had to do a bit of trickery.
If anyone wants to know how I did this, please send me an email

I would be interested in how this can be done.

 

[MajP]

Which Section would you build this VBA into?

You can build this in a standard module. You need to change this line to your names.
GetHistory = ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
This seems to work but it is a little clunky. It splits up the version history string and then does string manipulations to get rid of things you do not want. I emailed Isladog for how to view the history table. If that can be done then you potentially can loop the records and concatenate them yourself picking only the records you want.

 

[StuartG]

Which Section would you build this VBA into?

You can build this in a standard module. You need to change this line to your names.
GetHistory = ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
This seems to work but it is a little clunky. It splits up the version history string and then does string manipulations to get rid of things you do not want. I emailed Isladog for how to view the history table. If that can be done then you potentially can loop the records and concatenate them yourself picking only the records you want.

I'll be watching this space.
When you say split, is that where there is a gap between each update, as currently mine is all together and looks really messy.. be good for each comment to have a new line.. etc (Example below)

Version1: The cat sat on the mat

Version2: And it was sunny

 

[MajP]

In my example you should be able to do.

Public Function GetHistory(ID As Long) As String
  GetHistory = ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  GetHistory = CleanEmpties(GetHistory)
  GetHistory = RemoveLastEntry(GetHistory)
  GetHistory = replace(GetHistory,"[",vbcrlf & "[")
End Function

 

[isladogs]

I would be interested in how this can be done.

I've already replied to your request via PM.
However, the explanation is not something I want to put in an open forum thread

Incidentally, I get the column history on separate lines as each entry is a separate record. For example: