Re: How do I retrieve history from an append only field in Access

[AprilM]

*Thank you* for the response. I can tell this is exactly what I'm getting at
. . . it's unfortunately just a bit too high-level for me to grasp how I can
take this information and make it work for me in my database. I'm seeking
more simplified language and, perhaps, a guide that shows me why what code is
where so that I can look into making my own code from a very simplified
"building block" approach, if that makes sense.

"Brendan Reynolds" wrote:

>
> "AprilM" <Apr...@discussions.microsoft.com> wrote in message
> news:D93CAC27-BA14-4B54...@microsoft.com...
> > I have turned on the Append Only feature in Access 2007 so that my notes
> > field will log its history and I can essentially use my database as a CRM.
> > Now I want that history information to show up on a report. How do I code
> > the notes field to make that history show up in a separate table or memo
> > field with information in descending order (most recent date first) so
> > that I
> > do not have to right-click the field to see its history? And where do I
> > place that code?
>
> You don't need a table or field, Access stores that info for you, you can
> display it in a text box using the ColumnHistory function. Here's a link to
> the online documentation on the function ...
>
> http://msdn.microsoft.com/en-us/library/bb242869.aspx
>
> The feature is used in many of Microsoft's templates, for example, take a
> look at the Asset Details form in the Assets template to see how the
> ColumnHistory function is used.
>
> Now for the bad news. There's a bug that prevents tables containing
> append-only memo fields from being re-linked. You will have to delete and
> re-create links rather than refreshing them, programmatically or using the
> linked table manager, if you use this feature.
>
> --
> Brendan Reynolds
>

[AprilM]

Now I'm beginning to see the way this fits together. However, I've now
entered these expressions (where my table name Issues, column name Notes, ID
field Complaint ID):

=ColumnHistory([RecordSource],"Notes","[Complaint ID]=" & Nz([ID],0))
=ColumnHistory([RecordSource], "Notes","Complaint ID"=" & Nz([ID],0))
=ColumnHistory([RecordSource], "[Notes]","[Complaint ID]"=" & Nz([Complaint
ID],0))
=ColumnHistory([RecordSource], "Notes","Complaint ID"=" & Nz([Complaint
ID],0))
=ColumnHistory("Issues","Notes","ID=Complaint ID" = "& Nz([Complaint ID],0))

Each time I try and save the form, Microsoft Access gives me this error
message:
"The expression you entered has an invalid string. A string can be up to
2048 characters long, including opening and closing quotation marks."

Thank you for spelling this out for me, by the way!

"Brendan Reynolds" wrote:

> If you look at the Asset Details report in Microsoft's Assets template in
> design view, you'll see that the Control Source property of one of the text
> boxes looks like this ...
>
> =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))
>
> To view the history of your own append-only memo field, add a text box to
> your report, put the above expression in the Control Source property, and
> change the field names ("Comments" and "ID") to match your own field names.
> "Comments" in this example is the name of the append-only memo field, and
> "ID" is the name of the primary key field.