Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create a horizontal delimited list of records

How To

Create a horizontal delimited list of records

by  CosmoKramer  Posted    (Edited  )
Here is a way to create a horizonal list of records without the need for re-reading a recordset. It uses the detail already provided in the report.

But, this method requires hiding the detail section, so if that is not an option for your report this probably won't work.

In this example, ClassID is the group field, and Student the detail field.

1. Place the Student field in the detail section and set the section's Visible property to No.

2. In the group footer:
Place the ClassID field and an unbound text box (txtStudents) next to the ClassID field. You may have to set the CanGrow property of this text box to Yes.

3. In the Declarations section of the report, add a global variable:
Code: 
Option Compare Database  
Option Explicit
Dim FirstTimeThru As Integer

4. In the OnFormat event of the group header, enter code like this to initialize the variable and txtStudents:
Code: 
FirstTimeThru = False
Me!txtStudents = Null

5. In the OnFormat event of the detail section, put code like this to concatenate the students on each pass through the section:
Code: 
If Not FirstTimeThru Then
            Me!txtStudents = Me![Student]
            FirstTimeThru = True
         Else
            Me!txtStudents = Me!txtStudents & ", " & Me![Student]
         End If
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top