Excel MultiLine Textbox to Access Database - MultiLine lost in the Access Database

[macdonnie]

I have a Multiline textbox inside an Excel Userform. I enter in multiple lines of text:
excel_to_access_1.jpg

And then update the Access database. I no longer see text in access with multiplelines:
excel_to_access_2.PNG

I'm using the following code to update the database:

cmdCommand.ActiveConnection = conn
strSQLCommand = "UPDATE testTable SET testTable.stuffField = """ + comment + """ WHERE (((testTable.numberField)=""" + numberField + """) AND ((testTable.Book)=""" + book + """));"
cmdCommand.CommandText = strSQLCommand
cmdCommand.CommandType = 1
Set recSet = cmdCommand.Execute


conn.Close
Set conn = Nothing

When I look at the text from the Access Database I don't see carriage returns CR or LF(I copied the text into Notepad++). However, when I retrieve data from the access database and view it back in excel I do see that the text is multilined... (If I edit the text directly in access I can add multiple lines and do see the carriage returns)

The evidence leads me to believe that excel doesn't use standard carriage returns to display text in a textbox.

I have two questions:
1) Do you know if there is a difference in how Access stores newlines vs an Excel Textbox?
2) Do you see a way to adjust my code to send carriage returns to the Access Database? - BTW: I have tried adding Chr(10), Chr(13), and vbNewLine to the comment string and it didn't work.

Thanks,
-Don

[snb]

Did you try vbCrLf ?

[macdonnie]

Yeah I have tried vbCrLf too. Here is what I have tried:

comment = "Line1" + "\r\n" + " " + "\r" + " " + "\n" + "Line2" + "#xD #xA" + "Line3" + vbNewLine + " " + vbCrLf + " " + Chr(13) & Chr(10) + " " + Chr(10) & Chr(13) + " " + "

" + "Line4" + "
" + "5" + "
" + "6" + "


Interesting fact the   does show up as a space...

Thanks for the reply.

[snb]

To concatenate use &

"nbsp;" is HTML; I don't think VBA can interptret HTML.

I'd try:

vbCr
vbLf
vbCrLf

What does "\r\n" stand for ?

[macdonnie]

Changed the comment to:
comment = "1" & vbCr & "2" & vbLf & vbCrLf & "3" & "4"

The odd thing is that the output in access looks like the following:
1 2 34

This tells me that the vbCr/vbLf/vbCrLf is being transmitted to the database, however, access is not displaying them as new lines. Instead they are spaces.

If I'm in access I can manually add a record with a newline by selecting Ctrl + Enter. This does display as a new line.

It might not seem like a big deal that the records don't display as multiple lines, but it is since I am linking the database to a Sharepoint. The user can edit the database thru my tool or the sharepoint. However, when they edit the field via my tool all the text displays as one line.

The \r\n is a carriage return and new line feed. (I was trying to figure out what access uses for a carriage return and new line feed. To try to match the behavior of me manually entering in an entry and using Ctrl+Enter)

[snb]

You could try chr(7), chr(8) and chr(9) (in Word they are used for tables).

[macdonnie]

Ok so I kind of found an answer to my problem... The Access field is a Rich Text Format. If I disable RTF then vbCr/vbLf/vbCrLf worked flawlessly. ^_^

The negative is I can no longer save text as italics, bold, or etc...

OMG! I got it lol!

2 Minutes later...

Ok so I had additional code I forgot was running. The code retrieved the info from the access database and removed all html tags. All I had to do was add back the html tags and access interprets them as line breaks: <br></br> or <p></p>

Apologize for forgetting about the RemoveTags function. It was buried within ten thousand of lines of code.

Thanks so much for all your help!

[macdonnie]

BTW: Is there a way to mark the post as solved?