Concatenate New Line in Text Box - Is it Possible?

[jt463]

I have a report based on a query which pulls back schedules for new home construction. The report has an unbound text field that I want to write the activity descriptions to.

In other words, if I am pulling all activities scheduled for 5/2, I want to write each activity description to one text field.

For example: Install Appliances (scheduled for 5/2); Install Floor (scheduled for 5/2)

In this example, I want 'Install Appliances' to show up in my report text field (textrpt) and then add a line within textrpt and then append 'Install Floor' to textrpt. If that is not possible, I would like to append 'Install Floor' after a comma and space.

I am not familiar with the syntax for concatenating or even if that is what I would want to use.

Your time is greatly appreciated. If there is additional information I can provide to add clarity to my question, I will be happy to do so.

 

[hneal98]

Hold down the CTRL key and hit ENTER.

 

[MajP]

Private Sub Form_Load()
 'supply your text box name
 'you field name
 ' and an optional seperator
 Me.txtAllActivities = concatAllRecords("strFirstName", ": ")
End Sub

Public Function concatAllRecords(strFieldName As String, Optional strSeperator As String = "; ") As String
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  If rs.EOF And rs.BOF Then
    Exit Function
  End If
  Do While Not rs.EOF
    concatAllRecords = concatAllRecords & rs.Fields(strFieldName) & strSeperator
    rs.MoveNext
  Loop
  concatAllRecords = Left(concatAllRecords, Len(concatAllRecords) - Len(strSeperator))
End Function

 

[ItIsHardToProgram]

MajP would String separator work with "| "?

 

[jebry]

Hi!

VBA has a constant that will put in a line feed:

vbCrLf

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ItIsHardToProgram]

So following your direction, he would have to put in

Private Sub Form_Load()
 'supply your text box name
 'you field name
 ' and an optional seperator
 Me.txtAllActivities = concatAllRecords("strFirstName", ": ")
End Sub

Public Function concatAllRecords(strFieldName As String, Optional strSeperator As String = [highlight]VbCrLf + " "[/highlight]) As String
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  If rs.EOF And rs.BOF Then
    Exit Function
  End If
  Do While Not rs.EOF
    concatAllRecords = concatAllRecords & rs.Fields(strFieldName) & strSeperator
    rs.MoveNext
  Loop
  concatAllRecords = Left(concatAllRecords, Len(concatAllRecords) - Len(strSeperator))
End Function

 

[MajP]

Actually without changing the function as written

Private Sub Form_Load()
Me.txtAllActivities = concatAllRecords("strFirstName", vbCrLf)
End Sub

activity1
activity2
..
activityn

or

Private Sub Form_Load()
Me.txtAllActivities = concatAllRecords("strFirstName", "| ")
End Sub

activity1 | acitivity2 | activity3