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

Topic

New Reply

Referring to the sample database Asset Tracking, http://office.microsoft.com/en-us/template…3&av=ZAC000, I find an interesting expression in the Design View:
=ColumnHistory([RecordSource],”Comments”,”[ID]=” & Nz([ID],0))

Questions:
1. Where are the previous comments stored?
2. Can any of the previous comments be deleted? How?

Thanks.

Armstrong

Reply | Quote

Replies

Referring to the sample database Asset Tracking, http://office.microsoft.com/en-us/template…3&av=ZAC000, I find an interesting expression in the Design View:
=ColumnHistory([RecordSource],”Comments”,”[ID]=” & Nz([ID],0))

Questions:
1. Where are the previous comments stored?
2. Can any of the previous comments be deleted? How?

Thanks.

Armstrong

The Assets table has a Comments field, which is a Memo with the “Append Only” property set to Yes. This is designed to prevent any changes to previous entries, so that the field represent a version history that cannot be edited.
If you set the Property to No, all history will be lost, but otherwise any editing of previous entries is not possible.

I don’t know whether all the previous entries are stored in the one field (but hidden) or stored in a hidden table? My quick searching on this did not find an answer.

Reply | Quote

Hi John,

Your explanation makes a lot sense. The feature is interesting and useful.
BTW, is ColumnHistory() a function?
Thanks for your help.

Armstrong

Reply | Quote

BTW, is ColumnHistory() a function?

ColumnHistory is indeed a function; it is a method of the Application object.

See Application.ColumnHistory Method.

Reply | Quote

HansV,
Thanks for your help.
Besides the Memo fields, how about the Record Add/Edit?
Specifically, can each record be attached 2 fields:
1. Create Date
2. Last Edit Date

Armstrong

Reply | Quote

You’d have to add those two fields yourself.

1) A date/time field DateCreated with its Default Value property set to Now()

2) A date/time field DateModified that is populated in the Before Update event of the form(s) bound to the table:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.DateModified = Now
End Sub

If you only want to record the date instead of the date+time, use Date() and Date instead of Now() and Now.

Reply | Quote

Hi HansV,
Thanks a lot for your help.
Armstrong

Reply | Quote

Hi John,

How come in the Comments History each comment can be attached a precise Date/Time stamp?
Does it imply Access can actually attach Date/Time stamp whenever a field is added/edited?

Armstrong

Reply | Quote

How come in the Comments History each comment can be attached a precise Date/Time stamp?
Does it imply Access can actually attach Date/Time stamp whenever a field is added/edited?

In Access 2007, memo fields have now the capability of collecting change history. When you change an append-only field’s data, the change and time stamp are recorded and appended to the version history of the field.

Reply | Quote