Solved Access feature 'column history'

[HillTJ]

Hi, came across this command whilst researching my 'notes' control on a form. This is also thoroughly explained by Colin of Mendip Data systems. I have no experience with this feature, but it seems an ideal way for me to maintain a history of amendments to the note control. Should i use or avoid?

 

[June7]

1. only works with memo (long text) type field
2. kinda like a multi-value field
3. will cause headache if migrate to another db platform

If used, understand how to deal with its quirks. I have never used and probably never will.

 

[isladogs]

I wrote the article you are referring to, Column History Memo Fields - Mendip Data Systems, and on the surface, this may seem a useful feature.
However, I would also caution against using this. Like all features involving complex datatypes, the actual data is stored in a hidden system table over which you have no control.
New entries can be added and previous entries can be viewed...but not edited. Whilst that is usually a good thing, there may be occasions when it is necessary to edit or remove old data that is inappropriate. Doing so is a PITA!
A few months ago, I was contracted to restore such column history data to 'normal' datatypes in order to edit entries that didn't comply with GDPR.
There were thousands of entries and it took me several hours. Fine for me, but expensive for the client!

If the issues described aren't a problem for you, the feature works well.

 

[HillTJ]

Friends, Thanks again. I'll avoid this feature. As an amateur, it's difficult to determine the level of 'protection' I need to inbuild in anticipation of 'user' entries. It seemed that this feature was an easy way of capturing amendments to a record by users. I stand corrected!

 

[isladogs]

Just like using attachment fields, it is an easy way...but ease of use can lead to problems down the line

 

[June7]

Same recommendations and cautions apply to any type of multi-value field whether it be ColumnHistory, attachment, or text/number type field set to allow multiple values (looks like CSV string but isn't). All rely on hidden tables.

 

[gemma-the-husky]

I think I disagree. I don't think historical notes are necessarily part of "data". It depends whether you need it be searchable or just a record of occasional comments. It can be a simple useful add on.

There's two ways to do it I can see.
1) have a subtable of notes for each table record OR much simpler
2) have a single structured memo field, structed as date, entered by, comment, with a separator like a pipe symbol. Make the memo field non-editable, and have a "new note" option. Simply append "new notes" to the end of the memo field. You can now easily split the memo field into a 3 column list box (which is easy to do as you already have the row source), or a 3 column text box for display purposes.

 

[isladogs]

Hi Dave
Just out of interest, what/who are you disagreeing with?
The approaches you outlined are basically what I normally do.
The actual method used depends on whether the client wants previous entries to be read only or editable

 

[gemma-the-husky]

Hi Dave
Just out of interest, what/who are you disagreeing with?
The approaches you outlined are basically what I normally do.
The actual method used depends on whether the client wants previous entries to be read only or editable

I read the thread as both @June7 and @HillTJ not intending to use a notes feature, as it wasn't worthwhile. That was what I disagreed with - that it is relatively easy to get a useful facility with not much work.

Apologies if I misunderstood.

 

[isladogs]

Thanks for the clarification. I'll leave both of them to answer if they so wish

I agree the feature is fairly simple to do 'manually'. The method using column history may seem even easier but see my comments in post #5

 

[Pat Hartman]

I use a notes feature frequently. Most of the time old notes should not be updated. However, to fix errors that are found right away, i usually allow the creator of the note to change it for 24 hours. And the supervisor to change it for 48 hours. You can make allowances for holidays and weekends if that makes sense. You can adjust the change window times or prevent changes entirely, whatever the users want.

My notes table includes the datetime and the person who created the note as well as the note. This table is easier to work with and control than the notes "feature" that is built in.

 

[HillTJ]

gemma-the-husky, your post #7 option 1), is what I've been doing. Would it be too much trouble to see a demo of approach 2)?

 

[gemma-the-husky]

I won't do a demo, as it's straightforward.
I am using a comma as the record separator. You probably get issues if the note includes a comma, which is why I suggested the pipe symbol. I am not sure off hand whether you can set a different separator in a list box. I just assumed you could manage it somehow.

If you have a string such as

mystrg = "12/03/2021, Fred, Note 1, 12/03/2021, Bill, Note 2, 12/03/2021, Fred, Note 3, 13/03/2021, Mike, Note 4"

or more likely
mystrg = me. notesfield

Set up a 3 column list box
and make the rowsource equal to mystrg.

mylistbox.rowsource=mystrg (or)
mylistbox.rowsource=me.notesfield
then the list box will be shown as 3 columns. Note that the "dates" are just strings in the list box

If Bill then adds a new note in a control TxtNewNote, and maybe clicks a button, you just change the stored value

if len (me.notesfield)>0 then me.notesfield = me.notesfield & ","
    me.notesfield = me.notesfield & format(date(),"dd/mm/yyyy") & "," & environ(username)  & "," & txtnewnote
'you only need to have the leading comma after the first item

mylistbox.rowsource=me.notesfield
doevents 'may be needed to refresh the list box

 

[HillTJ]

Dave, Thank you. I just had to see it to understand it. Will have a play.

 

[HillTJ]

Dave, Cannot get it to work. Am unsure why, seems simple. Can you assist? I've attached a copy (work in Progress). If you could look at frmAsset, there is a notes control on bottom RHS. Really appreciate any help.

 

[gemma-the-husky]

See this.

1. You needed to change the names in the sample code to match your actual field/control names

2. I added a control to the from to hold the existing notes as an unformatted string. Just make this not visible.

3. I set the list box as a value list. For a value list you type in the row source you want. What I am doing is adding code to manipulate the row source, rather than you typing it in. The row source uses the comma as a separator, so you can't put commas in the text without it causing issues.

4. So when you add a new note, it appends the new note to the hidden text box bound to the notes field in the table, and then updates the row source of the list box with the new contents. In this version the newest notes now get inserted at the top. So
newnote = ''form new note"
existingnotes = newnote & existingnotes RATHER THAN
existingnotes = existingnote & new note

I am not used to tabbed forms, and I can't work out how to resize your form.
The notes field is in tblAssets, I entered a couple of notes on Asset7, and they ought to update as you move to other assets, but I don't quite see how the navigation is working.

Anyway, I hope this helps.

 

[HillTJ]

Dave, Big thanks. Such an elegant solution! Formerly, I was using a 1 to many linked notes table to do essentially the same thing. I'll use this technique in future.

 

[gemma-the-husky]

Pleased it made sense.

The trouble with a list box is it probably only works with short notes. You should be able to work out a way to pop up a form, and show the notes in a bigger/different format if the notes are longer

I actually used this to show a document revision history, and the notes were actually links to pdf's of the old versions. A list box is good because it comes with its own scroll bar - and you can select a row, and use the selected row - so its great for things like managing documents.

 

[Pat Hartman]

Going with the normalized design of having each note in a separate row allows more flexibility. For example, you can easily filter by time frame, or user in addition to searching for specific strings. And you can support or prevent updating as needed. And, it is a "no" code solution which I always prefer when possible. Stick to native features whenever possible.

 

[HillTJ]

Friends, after trialling the 3 column listbox technique, which worked well, and is certainly a technique I'll remember, I elected to go with the 1 to many 'normalised' design. Main reason was one of familiarity with the 1 to many format & flexibility in terms of filtering data. Just thought it appropriate to provide feedback to contributors whom assisted me in reaching this decision!