Split text to multiple rows in a textbox

[dgmdvm]

I have a simple form that passes the text from a single textbox (textbox11) in the form to a single textbox in a report (for printing). I need the text (which is delimited by a space) to display on separate lines in the report text box. I think I need to use the split function to do so but I can't seem to code for the carriage return. I've tried VbNewLine, and Chr(13). Should I split the text and create the multi line textbox in the form before it is passed to the report or should I split the text after the report opens?
(y is text from the form)
Me.Textbox11.SetFocus
Me.Textbox11.Text = y
Split(textbox11), VbNewLine

DoCmd.OpenReport "Test report", acViewPreview, , , acWindowNormal

Not sure what to do.

thanks

 

[arnelgp]

you can do it either way, split it and send to report or send it first then split it in report. the split part is:

Dim arrText() as variant
Dim i As Integer
Dim strText As String

arrText = Split(y, " ")

For i = LBound(arrText) To UBound(arrText)
strText = strText & arrText(i) & vbCrLf
Next
If strText <> "" Then strText = Left(strText, Len(strText)-1)
Me.Textbox11 = strText

 

[JHB]

Remember to set the "Text Format" for the text control in the report to "Rich Text"!

 

[Galaxiom]

Instead of Split() it could be done with Replace() in the textbox ControlSource to convert spaces to line breaks.

= Replace([fieldname]," ", Chr(13) & Chr(10))

You would have to name the textbox differently from the field in the RecordSource or it would get a circular reference error since the default for an unqualified name goes first to the Controls collection then to the Fields.

Also note that vbCrLf will not work here because it is a VBA constant that is not available on the report.

BTW

For i = LBound(arrText) To UBound(arrText)
strText = strText & arrText(i) & vbCrLf
Next

That is how most developers learnt to iterate an array. However a For Each loop is supported on arrays and is considerably more concise.

 Dim itm as Variant
  
 For Each itm in arrText
      strText = strText & itm & vbCrLf
 Next

 

[Galaxiom]

Remember to set the "Text Format" for the text control in the report to "Rich Text"!

That should not be necessary because line feeds render normally in plain text. Rich text is to support character formatting.

 

[dgmdvm]

Thanks for the responses. I get both the For loop and the For Each loops to work; I split the strings in the form. I had to change the type of arrtest to String, I got a type error when it was set to Variant. I'm getting my strings on separate lines now but the first line is always blank; it's as if I'm getting a Vbcrlf before I get my first string. Both loop codes behave the same way.

Thanks