Solved Problems Parsing Column History (1 Viewer)

[HillTJ]

Hi, I'm considering using field column History for capturing changes to a record 'Notes' on form 'frmPersons'. The record source is 'tblPersons'. I found this code by Googling and like the fact that it generates the history to a listbox. Code ceases where I attempt to parse datDate about 2/3 down the code (highlighted in orange). A type mismatch that I don't understand.

Appreciate some help. I'd also like the date to be displayed in dd/mm/yy format.

Cheers

Private Sub ShowColumnHistory(strTableName As String, strFieldName As String)
    'History data is in this format:
    '[Version: Date Time ] History Data
    Const VERSION_PREFIX As String = "[Version: "


    Dim strHistory As String
    Dim strHistoryItem As String
    Dim astrHistory() As String
    Dim lngCounter As Long
    Dim datDate As Date
    Dim datTime As Date
    Dim strData As String
    
    'Get the column history
    'strHistory = Application.ColumnHistory(strTableName, strFieldName, "")
    strHistory = ColumnHistory("tblPersons", "Notes", "[PersonID]=" & Nz([PersonID], 0))


    'Make sure there is history data
    If Len(strHistory) > 0 Then
    'Parse the column history into separate items.
    'Each item in the history is separated by a vbCrLf, but
    'if there are carriage-returns in the memo field data
    'you will get unexpected results. Split on the VERSION string
    'in the history data.
   astrHistory = Split(strHistory, VERSION_PREFIX)
    
    'Adding these lines ensures this code works regardless of
    'how the control is configured on the form
    Me.lstHistory.RowSourceType = "Value List"
    Me.lstHistory.ColumnCount = 3
    Me.lstHistory.ColumnHeads = True


    'Add column headings to the list box
    Me.lstHistory.AddItem "Date;Time;History"


    'Enumerate the history data in reverse
    'to fill the list box in descending order
    For lngCounter = UBound(astrHistory) To LBound(astrHistory) Step -1
        'Parse the history data
        strHistoryItem = astrHistory(lngCounter)


        If Len(strHistoryItem) > 0 Then
  
        'Parse the date from the history data.
        'This example parse the default US date format.
        datDate = CDate(Left(strHistoryItem, InStr(strHistoryItem, " ") - 1)) ' runtime error 13 type mismatch parses out date 12:00 AM
        strHistoryItem = Mid(strHistoryItem, InStr(strHistoryItem, " ") + 1)


        'Parse the time from the history data
        datTime = CDate(Left(strHistoryItem, InStr(strHistoryItem, " ] ") - 1))
        strHistoryItem = Mid(strHistoryItem, InStr(strHistoryItem, " ] ") + 3)


        'Add the history item to the list box.
        Me.lstHistory.AddItem datDate & ";" & datTime & ";" & strHistoryItem
        End If
    Next
    Else
    MsgBox "There is no history information for the specified field"
    End If
End Sub

 

[June7]

Issue is there is a space before date so the InStr() finds that space instead of one following date. Use Trim() function.

datDate = Left(Trim(strHistoryItem), InStr(Trim(strHistoryItem), " ") - 1)

Don't see need for CDate() function. Remove the arguments if you don't use them.

 

[HillTJ]

@June7, thanks. Yes the orange highlight didn't work but you saw the offending line of code. Will give it a go & let you know. Thanks.

 

[ebs17]

I think the additional use of a listbox at the latest leads the use of a memo field in connection with ColumnHistory ad absurdum.
You can use an additional table right away and thus stay in the area of the known and simple database technology.

 

[isladogs]

I also avoid using complex fields though compared to the use of MVFs and attachment fields, I think column history is comparatively innocuous.
So whilst I dont recommend them ,in case it helps, see my article on their use

Column History in long text fields

This explains how to use the Column History property to store historical data in long text (memo) fields

www.isladogs.co.uk

 

[HillTJ]

Thanks everyone. I once before dabbled with the 'column history' feature. I just liked the idea of parsing a formatted 3 column list box to display column history, thus this thread. I think, in real terms there is not a lot of advantage in using the column history technique. Colins treatise on the matter is excellent. I also played with the rowsource/textbox method which worked equally well.

Hmm, maybe i'll find a use for this technique someday!

 

[isladogs]

It can work well if used consistently but the standard approach with a related table and a 1:M join has almost identical functionality.
Problems occur when a client switches the feature on & off periodically and then wants to bin it but keep the data.
I once had 3 days work sorting out a complete mess of CHM data back to standard tables for a client. It was a nightmare to recover

 

[June7]

It's usually easier to combine than to separate (date/time parts may be exception). Use a normal related tables structure then if you want to mash records together, look at VBA to accomplish. One example http://allenbrowne.com/func-concat.html

 

[HillTJ]

All, I usually use a 1:many with a 'notes' table to allow many notes, dates, originators that kind of stuff to be retained. Looks like the column history feature is not much of a feature.

 

[ebs17]

Looks like the column history feature is not much of a feature.

You would have to give good reasons why working with a hidden system table that you don't really understand should be better than working with a normal table of your own.

I mean, this is (again) a feature that looks nice at first, but quickly leads to a dead end.

 

[HillTJ]

@ebs17 , you could not have said it better. A worthy exercise to prove the point.
Thanks.