Version History as a way of comment capture (1 Viewer)

[StuartG]

Hello All,

I use the following code in access as a way of capturing comments so my colleague and I know where we are at any given time, however I have noticed that the entries are not displaying sequentially?

=ColumnHistory("tbl - Issues","MemoField","[ID]= " & Nz([ID],0))

In the picture below it has kept sequentially but not in the way we want.

In the picture below, it's not in the right order. its shows newest to top and oldest to bottom?

How can I tell access to display it as newest first always.

Thanks
Stuart

 

[namliam]

Any select statement by its nature is random

Unless you add a seperate field that holds the date so you can add an explicit order by on the form/report/query, it will remain random.4
Alternative can be an autonumber PK assuming you enter your versions in sequence.

 

[StuartG]

Any select statement by its nature is random

Unless you add a seperate field that holds the date so you can add an explicit order by on the form/report/query, it will remain random.4
Alternative can be an autonumber PK assuming you enter your versions in sequence.

Thanks for the reply!
Its a rather strange one though as my colleagues PC generally puts it always in a sequential order, mine doesnt..

I don't quite understand what you meant in your latter comment, I thought ColumnHistory automatically put a date in, so not sure why I would need a dedicated field of date for the comments?

Stuart

 

[theDBguy]

Hi Stuart. I was wondering what the difference was between the images you posted, but you just answered it. So, you're saying the difference between the two is on which computer you're running the ColumnHistory code, right? If so, can you find out what may be different in the computer configurations between the two machines?

 

[StuartG]

Hi Stuart. I was wondering what the difference was between the images you posted, but you just answered it. So, you're saying the difference between the two is on which computer you're running the ColumnHistory code, right? If so, can you find out what may be different in the computer configurations between the two machines?

I can look but I am not sure if i will find the exact cause.
Generally the way i have created my DB is put the tables on a network drive, then the front end is distributed amongst me and my colleague, all amendments are done on my version and then my colleague takes a copy.

In light of this I would expect their version to behave in exactly the same way.
P.s. My colleague is not technical so wouldn't change any settings with the forms etc?

 

[theDBguy]

Hi. An internal function in Access wouldn't normally behave differently from one copy of an FE to another. If it does, then the likely candidates are the system configuration either in Access settings or the computer settings. For example, if you take one FE and make a copy of it and then execute both FEs on the same machine, then they should act the same way. But, if you take one of the copies and execute it on another machine; and it behaves differently, then the only difference between the two executions was the environment. So, I would tend to think of the computer settings as a suspect.

 

[isladogs]

Hi Stuart

Long time no see/hear. Hope all is fine. Are you still working for the same company?

Normally you would use a query based on the table and sort by the required field e.g. date
However the ColumnHistory feature depends on two deep hidden system tables which you cannot access or use for a query
You may find my website article useful: Use Column History to store historical data in memo fields

 

[StuartG]

Hi Stuart

Long time no see/hear. Hope all is fine. Are you still working for the same company?

Normally you would use a query based on the table and sort by the required field e.g. date
However the ColumnHistory feature depends on two deep hidden system tables which you cannot access or use for a query
You may find my website article useful: Use Column History to store historical data in memo fields

Hi There,

Indeed, yep all is good thank you. Still working for the same company, nearly two years now...

Thank you for the link, i will check it out..

 

[CJ_London]

column history is a special property for memo/long text fields over which you have no control with regards order. My understanding is it is a single field which you can only append too. Consequently the default is oldest first so not sure how you are getting newest first.

If you want to control order, you can't use the columnhistory method but instead you need to have a separate table for memos with one memo per record - Table to include other fields such as timestamp, author etc.

Ah - deep hidden tables, forgot about them

 

[StuartG]

Hi. An internal function in Access wouldn't normally behave differently from one copy of an FE to another. If it does, then the likely candidates are the system configuration either in Access settings or the computer settings. For example, if you take one FE and make a copy of it and then execute both FEs on the same machine, then they should act the same way. But, if you take one of the copies and execute it on another machine; and it behaves differently, then the only difference between the two executions was the environment. So, I would tend to think of the computer settings as a suspect.

Thank you for the advice.
I tend to complete alot of tweaks on my FE so it could be something has gone haywire in the background, but something not easily noticeable.

 

[theDBguy]

Thank you for the advice.
I tend to complete alot of tweaks on my FE so it could be something has gone haywire in the background, but something not easily noticeable.

Even if that's the case, those different behaviors should only happen "if" one computer is using a different tweaked version of your FE. Meaning, you could be using the version with the latest tweak, while the others are using an older version. Could that be the case? I just can't believe the same version of an FE would behave differently if they are run on the same environment. One test you could do is take a copy of somebody else's FE (the one that behave differently than yours) and then then run that copy on your machine (where the result was different than theirs). If you can do that, please let us know how it goes.

 

[StuartG]

Even if that's the case, those different behaviors should only happen "if" one computer is using a different tweaked version of your FE. Meaning, you could be using the version with the latest tweak, while the others are using an older version. Could that be the case? I just can't believe the same version of an FE would behave differently if they are run on the same environment. One test you could do is take a copy of somebody else's FE (the one that behave differently than yours) and then then run that copy on your machine (where the result was different than theirs). If you can do that, please let us know how it goes.

Yes its all very strange..

Prior to your message I had taken a copy of my colleagues FE and replaced my version, updated a record (Put a comment in) and again behaves differently.

So i am SG and my Colleague is AG
Now its putting the latest comment underneath..
This is the randomness of what I get..

There is more after 20/01 which is in sequential order however the next comment is the 31/01 which I would expect to be at the bottom.

Im going to check out Isladogs Columnhistory article.

I might have to look at the way I am recording the data and not use columnhistory as we use this audit field in our DB alot.

 

[theDBguy]

Hi. If you're storing the comment in a network located BE, then your colleague (AG) should now see your comment on their machine as well, correct? If so, do they still it in the same order as before (meaning, in a different order than yours)? If so, then that's what I was trying to say. Maybe the problem is because you're both using a different set of computers. So, the problem could be caused by some setting that is different between the two computers. Just guessing...

 

[isladogs]

As you will see from my article, ColumnHistory works well provided you don't need to modify it in any way.
By locking it away in a deep hidden system table, MS has greatly diminished the use of this feature.
I would advise doing it differently.

 

[StuartG]

Hi. If you're storing the comment in a network located BE, then your colleague (AG) should now see your comment on their machine as well, correct? If so, do they still it in the same order as before (meaning, in a different order than yours)? If so, then that's what I was trying to say. Maybe the problem is because you're both using a different set of computers. So, the problem could be caused by some setting that is different between the two computers. Just guessing...

Hi. Yes the comments are stored in a network located BE.. (AG) does see my comments on their machine.
She sees it in the same order as I do. on my machine.

 

[StuartG]

As you will see from my article, ColumnHistory works well provided you don't need to modify it in any way.
By locking it away in a deep hidden system table, MS has greatly diminished the use of this feature.
I would advise doing it differently.

Yes I have had a brief read of your article, need to study it alot more in depth though.
Im assuming there is no way of sorting the history box to show in date order.

I need to find some information in the forum that will give me the same ability as columnhistory but in a way i can sort it into a chronological order.

 

[theDBguy]

Hi. Yes the comments are stored in a network located BE.. (AG) does see my comments on their machine.
She sees it in the same order as I do. on my machine.

Okay, I take that as now you're saying there is no difference in what ColumnHistory is displaying between the two computers. Instead, the problem is either computer is not displaying it the way you and your coworkers prefer. Correct? If so, then as Colin said, since there's no way to change the standard behavior of ColumnHistory, you may have to use a custom solution. Good luck!

 

[StuartG]

Hi all,
I'm struggling to find the best way of not using ColumnHistory. I'd like to keep the view layout that it provides but be able to sort the comments into a chronological order.

Can you point me in the right direction?
@isladogs I read through your document but alot of it appeared to entail the Columnhistory function? (Unless I read it wrong)

Any support that can be provided would be great

 

[gemma-the-husky]

I haven't read every post

If your comments are just a single text entry, with the date embedded in the string as text - then you won't be able to sort these in date order based on the date. It will sort dd/mm/yy (or mm/dd/yy depending where you are). To get a text dictionary date sort, the date needs to be yy/mm/dd, or more correctly yyyy/mm/dd rather than simply yy/mm/dd (which was the cause of the year 2K problem).

 

[isladogs]

@gemma-the-husky
Sorry but that doesn't apply in this case as the data is stored in a deep hidden system table - see post #7

@stuart

The article was entirely about the ColumnHistory feature.
You will need a separate table with a one to many relationship to tblIssues.
Replicate the fields from the ColumnHistory tsble (see the article to see what is needed)
In particular, you need a memo field for your comments plus a date/time field so you can sort the comments by date in a query or recordset.

The issue you will have is retrieving the existing data.
If you only have a few items, you can go through manually, note the info & re-enter it in your new table
If there are a lot, I can retrieve the data for you by converting it back to a standard table.
However that would be chargeable
It needs a bit of trickery to do that but it should be relatively quick/inexpensive.
If you want me to do so, please email me