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:
15227

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

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?