Search code examples
sqlvbams-accesstextboxms-office

How to format textbox field in Access form to show data the same as in imported file to the table

I have a textbox, which is presenting data from one field in selected recordset. This field contains comments made by user in Excel file, which is later imported to Access table. Each comment is made in separete line and in each it looks ok. Textbox present it as one line, but when I export it to Excel again it looks ok in that new excel.

I have tried changing textbox into rich text format. I have also create other textbox, in which I am able to insert comments and then it looks good.

Textbox source code:

sSql = SELECT LogID, 1Comment, 2Comment, Author 
sSql = sSql & " FROM tblGeneralLog" 
sSql = sSql & " WHERE LogID= " & Me.ID &

Forms![frmMain_CommentAdd]![txtboxComment2].Value = CurrentDb.OpenRecordset(sSql).Fields(2).Value
Solution

  • Probably, in Excel you don't have a "full new line" (CR + LF). Thus, try:

    Forms![frmMain_CommentAdd]![txtboxComment2].Value = Replace(CurrentDb.OpenRecordset(sSql).Fields(2).Value, vbCr, vbCrLf)

    or:

    Forms![frmMain_CommentAdd]![txtboxComment2].Value = Replace(CurrentDb.OpenRecordset(sSql).Fields(2).Value, vbLf, vbCrLf)