Join the Column history of two fields in one textbox

[TonyD1016]

I am currently working with a database for multiple users that contains a Long text comment field for notes on customers. I was asked if it were possible to keep a history of the comments made in the field and the solution I used was to set the field as Append Only and use the following formula to display the column history in a separate text box on the form

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

That works for their purposes. They then asked if it were possible to track who was entering the comments, and the solution I arrived at was to use a custom function to do a Windows API call to trap the login name as a string and add a field to the customer table called "LastUpdate" and set the form to write that user name string as a default value to that field whenever the comment window is changed. That works for tracking who made the most recent entry but I was asked if it were possible to keep a history of who entered what notes.

Is it possible to set the LastUpdate field also as Append Only and to somehow marry that with the Comment field to display this information in the same text box? Or is there a better solution for what my co-workers are trying to do?

 

[Micron]

Sure, that would be OK. If I implied otherwise I didn't understand what you were saying, so apologizing for that.

 

[TonyD1016]

Sure, that would be OK. If I implied otherwise I didn't understand what you were saying, so apologizing for that.

No not at all. I really appreciate all of your help. I am not very good at explaining my intentions sometimes.
On top of that I am largely self-taught with Access and very much learning as I go.

But my takeaway is that the preferable approach would be to have a separate table that just tracks the comments, and have each entry be it's own record in that table.

So to facilitate my needs could I run a Descending query of the new Comment table to display only the Top result and set that query as the default value of an unbound textbox that will be disabled by default. Then when a user triggers a new record by changing the date in the date in the "Latest Update" field I can have the Dirty event enable the textbox allowing them to make the edits in what will then be saved as a new record.

I realize that's probably an odd way of doing this, but in the interest of working within the existing form as much as possible, would that work?

 

[Micron]

Yes, something like SELECT TOP 1 * . . . WHERE . . . ORDER BY CmtDate DESC
However, IMO tblCmnts was supposed to have the repeating FK field where the values are Customer PK's, so you could join tblCmnts.CustIDpk on tblCmnts.CustIDfk and make that form field bound, thus easily shows the latest record. Then I'd use a button to either:
- add a new record pre-populating date field and user enters comment, or
- open a popup and modal form where user enters comment only. I would not use update to date field to start a new record if that's what you're saying. You don't want the date to change for a prior comment that the date is supposed to align with.

You could just have unbound forms but IMO the extra work and code seldom is justified when you can bind. If you join the comment table as suggested, get a query working that is still updatable. If you cannot edit, that would have to be fixed first.

 

[TonyD1016]

Yes, something like SELECT TOP 1 * . . . WHERE . . . ORDER BY CmtDate DESC
However, IMO tblCmnts was supposed to have the repeating FK field where the values are Customer PK's, so you could join tblCmnts.CustIDpk on tblCmnts.CustIDfk and make that form field bound, thus easily shows the latest record. Then I'd use a button to either:
- add a new record pre-populating date field and user enters comment, or
- open a popup and modal form where user enters comment only. I would not use update to date field to start a new record if that's what you're saying. You don't want the date to change for a prior comment that the date is supposed to align with.

You could just have unbound forms but IMO the extra work and code seldom is justified when you can bind. If you join the comment table as suggested, get a query working that is still updatable. If you cannot edit, that would have to be fixed first.

I get what your saying. I already have the form set up to fill in the username automatically to a new record and it would be easy to do the same with the date.

Thank you so much for all your help. I'm going to mark the initial response as the solution since that is what prompted all of this discussion.

It's good to know there's such a great resource out here if I run into any more trouble.

 

[Micron]

Good luck with your project!