Transfer, timestamp and lock comments

[Nadine67]

Hello and thank you in advance for any attention this post may receive

I am running MS Access 2010 on Windows XP.

I have a form called 'Capture Form' and on it are two text boxes - Comments and Comments History. Comments is a Memo data type which is in table 'Improvement ideas'. Comments History does not appear in any table - should it?

Comments will be entered into 'Comments' and upon saving the form the text from 'Comments' will clear and be timestamped and entered into 'Comments History'. Each time comments are added to a record the same will happen yet it will append to the existing historical comment in 'Comments History'.

The Control Source for Comments History is =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

When I am in Form View I can enter comments into 'Comments' text box - great. 'Comments History' text box has #Error in it.



Where have i gone wrong?

[CJ_London]

Sounds like you are not using the feature as intended - take a look at these links

http://www.fmsinc.com/MicrosoftAcces...tory/Index.asp
https://support.office.com/en-us/art...7-c2bb6283535a

[Missinglinq]

Comments is a Memo data type which is in table 'Improvement ideas'. Comments History does not appear in any table - should it?

Comments will be entered into 'Comments' and upon saving the form the text from 'Comments' will clear and be timestamped and entered into 'Comments History'.

If your explanation is correct, I've done this before, in a situation where you don't want users to be able to edit a comments Field, but only add subsequent comments, to correct an entry, if necessary. And if our explanation is correct...you've got everything backwards!

The 'Comments' Field should be Unbound (not in any Table) and the 'Comments History' Field should be Bound to a Field in the underlying Table! After all, it's the 'Comments History' that you want saved and available, at a later date!

Linq ;0)>

[Nadine67]

Thank you to both of you for your help.

So Linq I have done what you suggested but am not sure how to get my 'comments' to append with a timestamp in 'comments history'.

Cheers nadine

[Missinglinq]

Here's a tutorial I wrote a number of years ago in response to someone with similar requirements; hopefully it'll help you.

In this example, there are two Memo Field Textboxes, but only one is Bound, since the other one is simply a temporary holding area. The Bound Memo Field Textbox, named CommentsField, here, must have its Locked Property set to Yes/True, so that all data entry has to be done through the temporary Textbox.

TempDataBox is Unbound, and in the Property Pane its Visible Property must be set originally set to 'No.' I place mine side by side with the CommentsField Textbox so that the user can refer to what's currently in the CommentsField Textbox while entering new notes.

Once again, the CommentsField Textbox is Bound to the Form's underlying Table/Query, and its Locked Property is set to 'Yes.'

Place a Command Button on the Form. Name it IndirectDataInput and in the Properties Pane set its Caption to "Add New Data"

Now use this code:

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
   TempDataBox.Visible = True
   TempDataBox.SetFocus
   IndirectDataInput.Caption = "Commit Data"
Else
   IndirectDataInput.Caption = "Add New Data"
   If IsNull(Me.CommentsField) Then
      If Len(Me.TempDataBox) > 0 Then
        Me.CommentsField = Now() & "  " & Me.TempDataBox
        Me.TempDataBox = ""
        TempDataBox.Visible = False
      Else
        TempDataBox.Visible = False
      End If
    Else
      If Len(Me.TempDataBox) > 0 Then
       Me.CommentsField = Me.CommentsField & vbNewLine & Now() & "  " & Me.TempDataBox
       Me.TempDataBox = ""
       TempDataBox.Visible = False
 
      Else
       TempDataBox.Visible = False
      End If
      
    End If
End If
End Sub

So, you click on the Command Button, the temporary Textbox appears, you enter your new data, click the Button again, the data is added to the Memo Field Textbox, and the temporary Textbox disappears.

You'll have to replace CommentsField, in the code above, with the actual name of the Textbox on your Form that is Bound to your Memo Field.

Linq ;0)>

[CJ_London]

@Linq - I think the OP is asking about the memo history available from access 2010 - a new property called append only (right at the bottom of the field properties, you usually have to scroll down to see it)