My Table is not updating with the second entries in Long Text field

Question

I am running below code in my Access 2016 to keep history of Comments.

Where AddNewComment is an Unbound TextBox & Comments is a TextBox field which is not "Enabled". So once user enter text in AddNewComment and click Save Comment Button it update the Comments box with Date & TimeStamp.

Issue is even if i will have more than one comment i can only see first comment in the table; can't view other comments in table.

Field Name is "Comments" data type is "Long Text".

**Code** 

Private Sub Save_Comment_Click()

If (IsNull(AddNewComment.Value)) Then
    MsgBox ("Please Enter a Comment before clicking" & _
            "on the Save Comment buttonn.")
    Exit Sub
End If

If (IsNull(Comments.Value)) Then
    Comments.Value = AddNewComment.Value & " ~ " & _
        VBA.DateTime.Date & " ~ " & VBA.DateTime.Time

Else
    Comments.Value = Comments.Value & _
        vbNewLine & vbNewLine & _
        AddNewComment.Value & " ~ " & _
        VBA.DateTime.Date & " ~ " & VBA.DateTime.Time

End If

    AddNewComment.Value = ""

End Sub

I prefer vbCrLf. Are you sure the revised string is not in table? Did you make the row height taller? Recommend a related table and each comment is a new record. — June7
Thank you so much.. It was the row height. I never thought it would be that but that was it. Thanks so much for your help and prompt reply. And i have changed my code to vbCrLf.. — meet shah
Just did a test with vbNewLine. Doesn't really seem to be any difference. Just never use vbNewLine since I learned about vbCrLf first. So got curious and found jaypm.com/2012/08/… — June7
Yeah, I was going to say - cbCrLf is identical. I like that vbNewLine sounds like what it is. Most of us haven't used typewriters enough to be familiar with the concept of "carriage return" and "line feed" imho. — Isaac Reefman
You can actually move between rows in my version of access in the table by clicking inside the field (as though you're about to edit it) and just hitting the down arrow. Each comment is it's own line. — Isaac Reefman

Isaac Reefman Isaac Reefman · Accepted Answer · 2018-05-24T04:19:18

To avoid confusion with disappearing comments in the bottom of the field (as mentioned in the comments), you could keep the most recent comments on the top. This way you'd always see whatever is most recent, even if the field size on the form is too small for everything.

You can also shorten the whole code by using the Nz function, replacing the null value in an un-updated Comments with an empty string ("").

Private Sub Save_Comment_Click()

If IsNull(AddNewComment.Value) Then
    MsgBox ("Please Enter a Comment before clicking " & _
            "on the Save Comment button.")
Else
    Comments.Value = AddNewComment.Value & " ~ " & _
        VBA.DateTime.Date & " ~ " & VBA.DateTime.Time & _
        vbNewLine & vbNewLine & Nz(Comments.Value, "")
    AddNewComment.Value = Null
End If

End Sub

My Table is not updating with the second entries in Long Text field

1 Answers