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]

IMO you're making it too difficult. Comments should be in their own table, each one as a record and not an append to a record. Each record, aside from the PK field, would have a foreign key field (FK) that is the PK value of the parent table, whatever that is. If I was a betting person, I'd say those who are asking would not want to see multiple comments as records; rather some jumble of perhaps tens of thousands of characters, interspersed with details that should be in fields. If not, then comments as records is definitely the way to go. Best part of that might be that each comment record can have a date and user field that provides those details. The comment field probably should not be editable once saved. That is how it was done where I worked and I cannot see a downside to it.

To answer your question directly, if you append-only to date records (not possible to append date values to the same record in a date data type field) you will not be able to link the last record to a particular portion of the comment field value. Nor would you be able to link a portion of a text field with multiple dates to a text field with multiple concatenated comments. The 1 record to 1 comment is the way to go. Very easy to retrieve comments by user/date/date range/job/whatever.

 

[jackd]

I agree with micron's suggestion. I have seen many instances where people have used long text/memo fields and keep appending or prepending dated comments with various identifiers. This gets to be a mish-mash of data that "simulates a table of records", but is difficult to manipulate for reports or analysis. Better to create a designed table and store/display whatever records are needed as appropriate.

 

[TonyD1016]

Thank you both for your replies.

This system has between 250 and 300 active customers at any one time. New customers are added and old ones are archived on almost a daily basis.
Currently the way the table set up is that each customer ID is a primary key and has a unique entry on the table.

If I am understand, your suggestion is that a better setup would be to have a separate table that just tracks the comments, perhaps with an Autonumber PK, and includes the Customer ID as a non-unique field? And then rather than use an Append Only field with the ColumnHistory function, I would run a query on the child table that sorts that data however they want it displayed on the form?

If so, what would be the best way to set up the form to display the customer information along with the current comment field, yet allow the field to be edited with the new information? This is a rich text field so is it possible to navigate to a new record and have the most recent information be displayed as a default value?
It is also a split database, so how what would be the best way to handle record locking to prevent users from accidentally editing the same customer?

 

[Micron]

your suggestion is that a better setup would be to have a separate table that just tracks the comments, perhaps with an Autonumber PK, and includes the Customer ID as a non-unique field?

Exactly.
Since there would be many comment records, a main form with customer etc. details and a subform below for the comments. Use Master/Child subform properties to link the comments FK field with the customer PK field. Not sure if your sf would has to be a continuous form to show rtf but I think it would be a better choice regardless.

is it possible to navigate to a new record and have the most recent information be displayed as a default value

I think you mean have the sf go to the last comment record when the forms open? Yes, using MoveLast or if the last 6 records for example:
- subtract your desired number from the record count and GoTo the record using that result. The record numbers and autonumbers don't necessarily jive, so note that I'm not saying PK autonumber value there. Or,

- resize subform control to show only x number of records, as long as you don't redesign the detail section of the subform

what would be the best way to handle record locking to prevent users from accidentally editing the same customer

This means comments? If you're not allowing the editing of prior comments then this is not an issue as each is a new record at that time. If it means customer details on the main form, then how do you handle that now? If it hasn't been an issue, I would not worry about it.

 

[TonyD1016]

I think you mean have the sf go to the last comment record when the forms open? Yes, using MoveLast or if the last 6 records for example:
- subtract your desired number from the record count and GoTo the record using that result. The record numbers and autonumbers don't necessarily jive, so note that I'm not saying PK autonumber value there.

To clarify, the form currently displays the Comments as a textbox for each customer in the table. The changes are usually minor and always incorporate existing comments. There is already a field to put in a date of the newest edits (stored as short date format in the customer table as a LastUpdated field). Whenever someone makes a change they simply put in the date and then make the edits to the comment field on the form.

Is there a way to preserve this setup? Since the the Comment field on the form is would be pointing to an entirely different table, is there a way to structure the form so that it displays the current comment in the textbox, but then instead of making the comment area blank for the new record when the date is changed, it just has the most recent comments as a default value to be easily edited?

 

[Micron]

I don't think you've grasped what jackd and I are saying. The suggestion looks something like this

On top would be your customer data. Below is the subform. This one has one record showing. The next comment record would be appended below the 1st. Instead of id345 you'd have the date the comment was entered, or if you're going to allow edits, the date it was edited. Perhaps next field would be who added it, followed by the comment. If you're still looking to append comments into an existing textbox, forget the additional table and this form idea. However, I think this method gives you exactly what you originally asked for:

I was asked if it were possible to keep a history of who entered what notes.

There would be no way I know of to relate an edit in a portion of a big string if that's what you're still after. In other words if you want to know that I changed a comment from this

"The customerr is one of our bst customers"
to
"The customer is one of our best customers"

then I not only don't know how, I wouldn't even want to try and figure it out.

 

[TonyD1016]

I don't think you've grasped what jackd and I are saying. The suggestion looks something like this

View attachment 61674
On top would be your customer data. Below is the subform. This one has one record showing. The next comment record would be appended below the 1st. Instead of id345 you'd have the date the comment was entered, or if you're going to allow edits, the date it was edited. Perhaps next field would be who added it, followed by the comment. If you're still looking to append comments into an existing textbox, forget the additional table and this form idea. However, I think this method gives you exactly what you originally asked for:

There would be no way I know of to relate an edit in a portion of a big string if that's what you're still after. In other words if you want to know that I changed a comment from this

"The customerr is one of our bst customers"
to
"The customer is one of our best customers"

then I not only don't know how, I wouldn't even want to try and figure it out.

Yeah for our purposes that's pretty close to what I am working with.
If we pretend that "Standard Name" would be the Comment box, what I was hoping for was to have it be if the user wants to make an edit and puts in a date in the Date field--which in the proposed setup will create a new record in the Comment table--to then have the Comment box on the form display the value of the most recent Comment for that customer in the Comment textbox for easy editing.
Can that not be done? Even with VBA?

 

[Micron]

If we're only talking edits here, then you don't need "Standard Name" textbox as you have it. You do the edit in the subform record or abandon the whole idea and just edit your notes textbox. However, this

I was asked if it were possible to keep a history of who entered what notes.

is not practical or perhaps even possible doing it your way. Each edit/creation would have to be a distinct record, otherwise you can only capture who made the last change and when and see the final result. If I'm wrong about that then I have no knowledge of a practical method so I can't comment further on the idea. I would not consider it practical to capture the before text and the after edit text as separate notes that you post the username and date to.

If going the distinct record route then I'm not sure I'd let the user enter the date either. I'd simply write the date to the record in the subform BeforeUpdate event so that it can't be fudged, plus it's just an unnecessary user step IMO. If you only want to see one note because this is all about minor edits, but want to store who/when, then run with the proposal but don't load all the comment records into the sub form - just load the one with the greatest date that pertains to the customer, then you just need a report or something to view the history.

 

[TonyD1016]

So you wouldn't recommend trying to set it up such that, using your previous form example, when making a new record on the subform-- id 346-- to have the default value of the field st_name in the new entry start out as the value of the previous entry?