Column History in Continous Form (1 Viewer)

Hanz · Aug 9, 2018

How can i display the access columnhistory in a text box in a continuous form? I tried to use this code :

Private Sub Form_Load()
Me.txtHistory = Application.ColumnHistory("tbl_RegistryJob Query", "JobTimeline", "ID=" & Nz(Me.txtID, 0))
End Sub

but when my form loads, all the column history on my form displays the same result. What i expect is that me.txtHistory will displays the history of whatever ID it shows on me.txtID on its left.

The_Doc_Man · Aug 9, 2018

I've never actually played with this, but I've read many issues with continuous forms. First, there is an "Append Only" requirement for column history. Second, I recommend a search of this forum for issues in continuous form displays. The problem is most likely due to it being a continuous form.

MajP · Aug 9, 2018

Since this is an unbound text box you would have to make a calculated control. I would use a public function

Code:

= getColHist([txtID])

Code:

Public Function GetColHist(ID As Variant) As String
  GetColHist = Application.ColumnHistory("tbl_RegistryJob Query", "JobTimeline", "ID=" & Nz(ID, 0))
End Function

CJ_London · Aug 9, 2018

columnhistory relates to memo/long text fields. and the method only works with tables

your table name has a space so probably needs to be in square brackets - and the name doesn't sound like it is a table where the memo field was populated

And your field name does not sound like a memo field

your code looks like it is populating an unbound textbox - so it will show the same value for every field - and anyway should not be necessary, just bind the textbox to the memo field, no code required

As Doc says, Append Only needs to be set to yes - it is right at the bottom of the field properties in the table and you need to scroll down to see it. Until you set it you will not retain any history - not sure what happens if you set it having already populated fields - probably not a problem, but you will have to try it and see.

Hanz · Aug 10, 2018

The column is set to long text and the append only is set to yes. I know that my column history code works because it already gets the history of the field on my table. my only issue is, when I do

Me.txtHistory = Application.ColumnHistory("tbl_RegistryJob Query", "JobTimeline", "ID=" & Nz(Me.txtID, 0))

My text boxes in my continuous form all display what is the history of the first ID on my table. What I expect is to put different history in this text boxes according to what is in my ID text box in my continuous form.

i tried MajP suggestion, I went to the code window of my form, create that public function and on the control source of my text box i put the

= getColHist([txtID])

as my me.txtHistory control source. yes i got all the history of all the the records on different ID but it lumps all the history in my text boxes. meaning history in my text boxes in my form are all the same.

I don't know if I got MajP point correctly. Please elaborate more for a novice like me.

June7 · Aug 10, 2018

No VBA. Expression in textbox ControlSource:

=ColumnHistory("tbl_RegistryJob Query", "JobTimeline", "ID=" & [ID])

Hanz · Aug 10, 2018

June7 This works for me. Thank you.

Is there a way that I can arrange the sequence from Latest to oldest? I mean the access columnhistory is displayed from oldest on top to the oldest below. How i can reverse this that what will show on my text box is from latest to oldest

June7 · Aug 10, 2018

Not without VBA code to parse the string and rebuild it.

Cronk · Aug 11, 2018

I've not tried it but maybe the resulting text string, presumably with carriage returns, could be exported to a text file and a sorting query based on a linked table to the text file.

Hanz · Aug 11, 2018

i found a code to parse the history but it was originally be displayed in list box. What I want instead is to display this in a textbox. I tested the code below and it displays the History individually in a msgbox. I just don't know how i can concatenate these results inside a textbox and maybe from here i can bind my textbox to a table field and will resolve my original question on columnhistory on continuous form.

Private Sub Command3_Click()

Const Version_Prefix = "[Version: "

Dim StrHistory As String
Dim aStrHistory() As String
Dim lngCounter As Long
Dim HistoryResult As String

StrHistory = Application.ColumnHistory("tbl_RegistryJob Query", "JobTimeline", "ID=" & Nz(ID, 0))
aStrHistory = Split(StrHistory, Version_Prefix)

For lngCounter = UBound(aStrHistory) To LBound(aStrHistory) Step -1

MsgBox aStrHistory(lngCounter)
' Me.txtHistory = HistoryResult & vbCrLf
Next lngCounter

End Sub

June7 · Aug 11, 2018

Make this a function and call the function from textbox with: =GetHist(Nz([ID],0))

Code:

Public Function GetHist(intID)

Dim aStrHistory As Variant
Dim lngCounter As Long

aStrHistory = Split(Application.ColumnHistory("tbl_RegistryJob Query", "JobTimeline", "ID=" & intID), "[Version: ")

For lngCounter = UBound(aStrHistory) To LBound(aStrHistory) Step -1
    GetHist = GetHist & Trim(aStrHistory(lngCounter)) & IIf(lngCounter = UBound(aStrHistory), vbCrLf, "")
Next lngCounter

End Function

Problem with display this string or the original ColumnHistory in textbox is the string is so long it doesn't all display. The CanGrow property doesn't seem to work and the vertical scroll bar doesn't activate. When the textbox has focus, up and down cursor keys will move through the lines.

Hanz · Aug 11, 2018

Great! I got my form work as i expected.

Thanks June7.

I'm okay if my text box won't show all the history. the remarks on each history are usually 5 to 10 words and i can fit it to to a longer text box and maybe a 1.5" tall text box just to show the last three history. User can always use down arrow to go to the first remarks in the history. I wanted to use listbox but it seems conditional formatting doesn't apply to that.

I just copy your code. I still need to understand it.

Of course, my thanks to all who shared their ideas to this thread. I rate this thread, Excellent!!!

The_Doc_Man · Aug 11, 2018

June7, the .CanGrow property is (apparently) meant only for printed items because in a display, you can instead set the text box to have scroll bars to handle long data. In PrintPreview on a report, .CanGrow works fine.

June7 · Aug 11, 2018

Yes, I know CanGrow works on report. I always forget about it working on printed forms. Thanks for the reminder. Looked for but couldn't find ScrollBar property for textbox, looked again and there it is above FontName property.

The_Doc_Man · Aug 12, 2018

Yep, it is there. When I first started using Access (version was 2.0), my thought was this combination: Oh, boy, with Access I can see everything I could possibly want to control. Oh, damn, with Access I see everything including stuff about which I have less than no clue about what it controls.

I took that attitude because of Borland Paradox, which didn't show you everything that Access shows you. Like a kid in a candy store, both bedazzled and bewildered.

Hanz · Aug 14, 2018

I thought i posted my reply. Anyways, Thanks the Doc_Doc_Man for the tip. I got my scroll bar for my text box.

Column History in Continous Form (1 Viewer)

Hanz

Registered User.

The_Doc_Man

Immoderate Moderator

MajP

You've got your good things, and you've got mine.

CJ_London

Super Moderator

Hanz

Registered User.

June7

AWF VIP

Hanz

Registered User.

June7

AWF VIP

Cronk

Registered User.

Hanz

Registered User.

June7

AWF VIP

Hanz

Registered User.

The_Doc_Man

Immoderate Moderator

June7

AWF VIP

The_Doc_Man

Immoderate Moderator

Hanz

Registered User.

Similar threads

Users who are viewing this thread