ColumnHistory results not in DateTime order (1 Viewer)

[gakiss2]

I am using ColumnHistory to keep track of changes to a form. When a control on the form is changed then a sub is called to add an entry to the Note field.
The code is very simple:

Public Sub AddHistory(frmControl As Control, ctlName)
Forms("frmDocDetail").Note = ctlName & " Changed to " & frmControl & " by " & Environ("Username")
Forms("frmDocDetail").Refresh
' fTabNextControl
Forms("frmDocDetail").Note = ""

End Sub

Here is some code of the sub being called, again very simple

Private Sub PartNum_AfterUpdate()
Call AddHistory(PartNum, "Part Number")
End Sub

As far as the detail of what is put into the Note field, all is working very well.

I should mention that I didn't like the long tag so I shortened it by getting rid of "Version:" by using this expression for the field:
=Replace(ColumnHistory("tblDocsIssued","Note","[ID]=" & Nz([ID],0)),"[Version:","")

that works too. It isn't code per se, it is entered in the properties for the Note field.

Form in Design View:

The problem is that the entries displayed in the control are meant to be in chronological order but the are not. It is difficult to describe. The individual lines seemed to be 'clumped' but the clumps are not in order. I haven't been able to discern any 'logic' to the order the history lines are presented in. Below is an image showing the 'clumped' incorrect order.

This seems to happen to most records. The lines start at the top if you open an existing form (made before today's date). If you make a new form then they are listed top to bottom as they are added. If you open that new form again, on the same day, then new lines add to the bottom as expected. But even that doesn't explain that sometimes the new added to an existing sometimes start in the middle somewhere.

Is there a setting or some code I can use so that the lines of history are presented in the order they are recorded?? Old to new top to bottom. I would actually prefer the reverse order, new on top. But getting it in some logical order is the first order of business.

Thanks for all the help.

 

[theDBguy]

Hi. I haven't use an Append Only Memo field other than in SharePoint, but if the order of the entries is really important to you, then perhaps you might consider using a separate table instead of a Memo field with ColumnHistory. Just a thought...

 

[gakiss2]

Yes, I actually had the same thought as that would give complete control of the output. I temporarily remain hopeful I can 'fix' what I have for now out of pure laziness

 

[theDBguy]

Yes, I actually had the same thought as that would give complete control of the output. I temporarily remain hopeful I can 'fix' what I have for now out of pure laziness

Well, then let's hope someone else has a brighter idea than us.

Eventually, you might still have to face the music. Good luck!

 

[MajP]

Any chance you can post an example? I do not know if there is some property, to fix this. I think there would be. You should be able to roll your own function to take the string and sort it. I have done something similar in the past to clean up the column history.

 

[isladogs]

The entries should be displayed in the order they are stored in the deep hidden table associated with the column history.
That means the list should always have the oldest entry first.
See my article http://www.mendipdatasystems.co.uk/column-history-memo-fields/4594523656 for more information.

Having said that, the column history property can be a total PITA to sort out when there are issues.
As a starting point, try removing the replace part of the code and see if that helps. It doesn't seem to be working anyway.

If you are still having problems I may be able to help but in the long run I suggest storing the column history data conventionally in a separate table with a one-to-many join. You will then have control over the data rather than it being done behind the scenes by Access