Using ColumnHistory Expression in an Form (1 Viewer)

S

StuartG

Registered User.
Local time
Today, 12:47
Joined
Sep 12, 2018
Messages
125
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.
 

Attachments

  • Column History.JPG
    Column History.JPG
    24.6 KB · Views: 209
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
That looks correct. make sure the memo field being referenced is set to AppendOnly. It will error if not.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
Column = AuditComments
Click to expand...
Not sure if typo but you have "comments" in the call.
 
S

StuartG

Registered User.
Local time
Today, 12:47
Joined
Sep 12, 2018
Messages
125
MajP said:
That looks correct. make sure the memo field being referenced is set to AppendOnly. It will error if not.
Click to expand...

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
No the field in the table design view has a property
"Append Only" which is yes or no.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
"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.
 
S

StuartG

Registered User.
Local time
Today, 12:47
Joined
Sep 12, 2018
Messages
125
MajP said:
"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.
Click to expand...

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
 

Attachments

  • History Error.JPG
    History Error.JPG
    24.4 KB · Views: 177
  • History Error - Code.JPG
    History Error - Code.JPG
    22.9 KB · Views: 174
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
You keep changing your names. Now you have "Notes"
Code: 
=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
Code: 
Public Function GetHistory(ID As Long) As String
   Debug.Print ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  Debug.Print "History" & GetHistory
End Function
 
S

StuartG

Registered User.
Local time
Today, 12:47
Joined
Sep 12, 2018
Messages
125
MajP said:
You keep changing your names. Now you have "Notes"
Code: 
=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
Code: 
Public Function GetHistory(ID As Long) As String
   Debug.Print ColumnHistory("EmployeesStr", "Notes", "EmployeeID = " & ID)
  Debug.Print "History" & GetHistory
End Function
Click to expand...

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)
 

Attachments

  • Working Article, with issues.JPG
    Working Article, with issues.JPG
    44.9 KB · Views: 169
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
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.

Code: 
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
 
S

StuartG

Registered User.
Local time
Today, 12:47
Joined
Sep 12, 2018
Messages
125
MajP said:
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.

Code: 
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
Click to expand...

Which Section would you build this VBA into?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 12:47
Joined
Jan 14, 2017
Messages
18,209
MajP said:
I know of no way to find where this information is really stored. May be in a system table.
Click to expand...

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
 

Attachments

  • ColumnHistoryExample.zip
    333.1 KB · Views: 175
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
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
Click to expand...
I would be interested in how this can be done.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
Code: 
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.
 
S

StuartG

Registered User.
Local time
Today, 12:47
Joined
Sep 12, 2018
Messages
125
MajP said:
Code: 
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.
Click to expand...

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

:D:D:D:D
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,525
In my example you should be able to do.
Code: 
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

isladogs

MVP / VIP
Local time
Today, 12:47
Joined
Jan 14, 2017
Messages
18,209
MajP said:
I would be interested in how this can be done.
Click to expand...

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:



 

Attachments

  • VHTable.PNG
    VHTable.PNG
    17.8 KB · Views: 688
  • Form with VH.PNG
    Form with VH.PNG
    13.3 KB · Views: 714
Last edited:
You must log in or register to reply here.

Similar threads

ardy
Solved Expression Builder Latest Date in Forms
Replies
10
Views
451
ardy
ardy
W
Copy a Num Feld to an other Form
Replies
5
Views
410
sonic8
S
T
Use expression in MS access
Replies
14
Views
750
techreader
T
H
move an item from list box to another list box in one form
Replies
6
Views
480
MajP
MajP
A
The Microsoft Access database engine does not recognize '[Forms]! [Form2]! [StartDate]' as a valid field name or expression.
Replies
8
Views
640
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom