Question Column History in MS Access 2007

[jagstirling]

Hi,

Can anyone explain (simply) the ColumnHistory functionality ?

I've seen it in a couple of MS Sample databases (Lending Library for example) but I can't replicate it in any of my databses.

It involves typing some text in a text box and then this and any other historical text is shown in a text box below.

I can see that it uses some kind of Record Source (not my strongest area). The folllwing appears in the historial text box;

=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

Surely there is some code associated with the above but I can't find anything anywhere.

Any help or advice gratefully received.

Thanks.

 

[RuralGuy]

What form and control are you referring to?

 

[mcclunyboy]

Hi your close to getting this working

(I know its been a few months but I just found the solution myself)

All you have to do is to copy =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)) into a blank text box.

Then go to your table and click on the memo field - then change the "append to" property to "Yes" (default is no)

Should work.

 

[lateefx]

To learn how this works simply open the SALES PIPELINE Access DB template that is free with Access 2007. You will find on the 'Opportunity Details' form there is is tab called 'Comments' -- this tab has two fields in it...one is 'Comments' and the other is 'History'.

'Comments' is the field that relates directly to the field 'Comments' in the datasheet ...
'History' is a field that is auto-populated with the different changes you have made to the 'Comments' field over time...with version date and time added on to each entry.

There is no corresponding field for 'History' within your datasheet -- all you have to do to make sure this works properly is type the following into the Control Source of your 'History' field: =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

Then...make sure that your original 'Comments' field's AppendOnly property is set to "YES" in your datasheet design view. Of course...the 'Comments' field must also be MEMO TYPE.

 

[srs09]

Is there a similar ColumnHistory functionality for access 2003? I've been looking into this for a while w/o results. Apologies for jumping into this topic but this is exactly what I've been trying to do.

 

[srs09]

No, it is new in 2007. You would have to use something like this Audit Trail to do it in 2003.

Thank you for fast response! Going to take a look now

 

[mmg]

Hi,

I've tried to create a column history in my database but for some reason I am getting a #error in the history text box. I put in the code =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)) in the history text box. The comments is set to memo and appendonly is set to yes. any suggestions on how to correct this issue?

(I made another comment/history box on my other table and forum but for some reason doesnt work for the second table and forum) the second table has a one-many relationship with the first table's primary key.)

 

[emijayne]

Hi,

I've tried to create a column history in my database but for some reason I am getting a #error in the history text box. I put in the code =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)) in the history text box. The comments is set to memo and appendonly is set to yes. any suggestions on how to correct this issue?

(I made another comment/history box on my other table and forum but for some reason doesnt work for the second table and forum) the second table has a one-many relationship with the first table's primary key.)

mmg -- I have found that I will occasionally receive that #error if my recordsource is not in quotes. Try this:

=ColumnHistory("RecordSource","Column","[ID]=" & Nz([ID],0))

 

[Amalthia]

I've tried pasting "=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))" into the unbound box and none of the history columsn show up. Am I supposed to substitute "RecordSource" for the table name? I have a Notes field and a Notes History but I'm unable to get the Notes History to appear on a report.

 

[JWChilders]

I am having a similar problem with the sample Customer Service data base.

I have tried modifying the Case Details form. I added a field for the Company name. This worked. The value was the value associated with the Customer name (person).

The problem is that History now displays "#Error". Removing the field for Company does not correct the problem.

I tried to duplicate the functionality of the History field by creating a similar field. The same results occur.

 

[chriscara]

I had this same problem but in Access2010. I found that the [ID] had to match the "ID field" in my table....which I changed to [WorkOrderID].....and it worked. To print, you can use the same statement, but be sure you use the correct field name there also.

Chris Cara

 

[AmyLynn0427]

Is there a way to edit the history? I've been using this field in the "Issues" template. Several times I closed the form without clearing out the previous comment so it duplicated the comment in the history. The result is some sloppy looking reports I have to send to Sr management.

 

[ksnead]

Is there anyway to add spaces between the history entries, so that it is not one huge blob of text?

 

[journeyman]

it's working fine for me however...
i would like to get rid of the word "version" in each line and also would like to get rid of the []

can anyone tell me how to do that?

 

[Pauly78]

im trying to add this to my database in Access 2013 and all i get is he #Error, its driving me mad, can anyone help? ive done exactly what it says in tis tread.

 

[CJ_London]

responders - please note that OP already has a thread on this subject.

http://www.access-programmers.co.uk/forums/showthread.php?p=1413229&posted=1#post1413229

OP - suggest you put a link to this thread from the other thread