How to add CR/LF to a forms .value text box

[cobolguy]

I am trying to add the cr/lf characters to a form text box. I have used both text and a memo field that is set for Rich Text. I can add them to a string, display the string in a message box - but when I move the string to the forms field the CR/LF are being stripped out. I do not know how to overcome that. Here is a snippet of code:

Private Sub ParentParcel_DblClick(Cancel As Integer)
'On Error GoTo EndSub
'
''''''
' 
'
''''''
Dim NewParentParcel As String
Dim CurrentParentParcel As String
Dim TempMapBookPage As String
Dim strSQL As String
Dim response As String
'
TempMapBookPage = "'" & MapBookPage & "'"
CurrentParentParcel = ParentParcel
TempMapBookPage = "'37/110'"
'MsgBox (TempMapBookPage)
strSQL = "SELECT * FROM ParentParcelsTable where MapBookPage=" & TempMapBookPage & "order by ParentParcels"
'
Dim dbs As DAO.Database
Dim rstMapQuery As DAO.Recordset
'
Set dbs = CurrentDb
Set rstMapQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
'
Do Until rstMapQuery.EOF
    If NewParentParcel = "" Then
      NewParentParcel = rstMapQuery.Fields("ParentParcels")
    Else
       NewParentParcel = NewParentParcel & vbCrLf & _
         rstMapQuery.Fields("ParentParcels")
    End If
    response = MsgBox(NewParentParcel, vbYesNo)
    ' after this statement I can see the parcel numbers displayed    
  rstMapQuery.MoveNext
'
Loop
'
EndSub:
'
rstMapQuery.Close
Set rstMapQuery = Nothing
Set dbs = Nothing
'
MsgBox ("final result = " & NewParentParcel)
Forms("RecordedMapsForm").ParentParcel.Value = NewParentParcel
Me.Refresh
'
End Sub

[ADezii]

To add a Carriage Return/Line Feed combination to any String:

strAnyString & vbCrLf

[cobolguy]

Thanks ADezii for the quick reply. Please take a look at line 30. At line 46 where the data goes back to the form - they are stripped out for some reason.

[ADezii]

Thanks ADezii for the quick reply. Please take a look at line 30. At line 46 where the data goes back to the form - they are stripped out for some reason.

Can't think of any reason why they would be. Instead of vbCrLf, you can use:

Chr$(13) & Chr$(10)

[cobolguy]

I have used vbNewline and the chr(n) codes as well. They all display correctly in the msgbox at ine 33, but after the statement at line 46 the strings are concatenated without the CR/LF. Maybe there is another work-around?

[ADezii]

I have used vbNewline and the chr(n) codes as well. They all display correctly in the msgbox at ine 33, but after the statement at line 46 the strings are concatenated without the CR/LF. Maybe there is another work-around?

Type the following into the Debug Window (CTRL+G):

? "Herman" & vbCrLf & "Munster"

You should see:

Herman
Munster

Is this the case?

[cobolguy]

Hello ADezii - yes I see it (without the 1. and 2. of course). It would be nice if I could include a screenshot of the msgbox to show you.

[ADezii]

Hello ADezii - yes I see it (without the 1. and 2. of course). It would be nice if I could include a screenshot of the msgbox to show you.

You never actually answered Post #6, do you see

Herman
Munster

when you type ?

"Herman" & vbCrLf & "Munster"
 

[cobolguy]

Yes - I see it in the Immediate Window as you said. Thanks.

[ADezii]

Yes - I see it in the Immediate Window as you said. Thanks.

Try the code in a Standard Text Box and see what happens.

[cobolguy]

It works in the standard text box. It seems to work everywhere except at line 46 - where it does not work. There is something the compiler is doing to remove the CR/LF's from the stream before in goes back to the form. The form field from line 46 looks like this:
09-2-03-1-001-008.000 09-2-03-1-001-009.000 09-2-03-1-001-010.001 09-2-03-1-001-010.002

I want it to look like this:

09-2-03-1-001-008.000
09-2-03-1-001-009.000
09-2-03-1-001-010.001
09-2-03-1-001-010.002

And this is what the msgbox displays.

[ADezii]

It works in the standard text box. It seems to work everywhere except at line 46 - where it does not work. There is something the compiler is doing to remove the CR/LF's from the stream before in goes back to the form. The form field from line 46 looks like this:
09-2-03-1-001-008.000 09-2-03-1-001-009.000 09-2-03-1-001-010.001 09-2-03-1-001-010.002

I want it to look like this:

09-2-03-1-001-008.000
09-2-03-1-001-009.000
09-2-03-1-001-010.001
09-2-03-1-001-010.002

And this is what the msgbox displays.

Would it be possible for you to send me the DB as an E-Mail Attachment? I'm currently on vacation, but the problem is that I am not running Access 2007 but when I get into work next week, I'll be more than happy to have a look at it for you. Kindly let me know one way or the other.

[cobolguy]

Thanks ADezii for the extra nice offer. I would not put you out like that. Maybe, I will continue to experiment for other work-arounds and hit on something. I'll repost if I get a solution. Thanks much.

[cobolguy]

I found a work-around for the problem. You cannot have the field on the form set for Rich Text. I deleted the field from the form. Saved/Closed the form and changed the table field from Memo to Text, size=255. Re-added the field back to the form and re-ran the same procedure. This time it produced the same results as the msgbox had been displaying all along. The Rich Text option must cause the CR/LF to be removed. I hope this helps someone else in the future.

[ADezii]

I found a work-around for the problem. You cannot have the field on the form set for Rich Text. I deleted the field from the form. Saved/Closed the form and changed the table field from Memo to Text, size=255. Re-added the field back to the form and re-ran the same procedure. This time it produced the same results as the msgbox had been displaying all along. The Rich Text option must cause the CR/LF to be removed. I hope this helps someone else in the future.

Thanks for sharing this information with us.