ACC: How to Concatenate a List of Items from a Many-Side Table

MORE INFORMATION

1. Create the following query as the basis for the sample report:

         Query: qryCategoriesProducts
         -------------------------------------------------------------
         Type: Select Query
         Join: Categories.[CategoryID] <-> Products.[CategoryID]
               (or Categories.[Category ID] <-> Products.[Category ID]
               in Microsoft Access 2.0)
         Field: CategoryName (or Category Name in Microsoft Access 2.0)
            Table: Categories
            Sort: Ascending
         Field: ProductName (or Product Name in Microsoft Access 2.0)
            Table: Products
            Sort: None
   					

2. Create the following report:

         Report: rptCategoriesProducts
         -----------------------------------
         Caption: Products by Category
         RecordSource: qryCategoriesProducts
   					

3. Create the following Sorting and Grouping sections in the report:

         Field Expression: CategoryName
            Sort: Ascending
            Group Header: Yes
            Group Footer: Yes
   
         Field Expression: ProductName
            Sort: Ascending
            Group Header: No
            Group Footer: No
   					

4. Name the CategoryName header section grpHeaderCategoryID and set the header section's Height property to zero (0).
5. Place the ProductName field in the report's detail section and set the detail section's Visible property to No.
6. Place the CategoryName field in the report's Category Name footer section.
7. Add an unbound text box named AllProducts to the report's Category Name footer section.
   
   NOTE: You may want to set the CanGrow property of the AllProducts field to Yes if you anticipate that the number of items may exceed the width of the text box. This enables the text box to grow vertically.
   
   NOTE: Place the CategoryName and AllProducts fields side by side for clarity when you print the report.
8. Add the following lines to the report's Declarations section if they are not already there:

         Option Compare Database  ' Use database order for string comparisons.
         Option Explicit
         Dim FirstPass As Integer
   					

9. Add the following code to the CategoryName header section's OnFormat property [Event Procedure]:
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Sub grpHeaderCategoryID_Format (Cancel As Integer, _
                                         FormatCount As Integer)
            Me!AllProducts = Null
            FirstPass = False
         End Sub
   					

10. Add the following code to the detail section's OnFormat property [Event procedure]:

          Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
             On Local Error GoTo Detail1_Format_Err
             If Not FirstPass Then
                Me!AllProducts = Me![ProductName]
                FirstPass = True
             Else
                Me!AllProducts = Me!AllProducts & ", " & Me![ProductName]
             End If
          Detail1_Format_End:
             Exit Sub
          Detail1_Format_Err:
             MsgBox Error$
             Resume Detail1_Format_End
          End Sub
    					

11. Close and save the report.
12. Open the report in Print Preview. Note that the following data is displayed in vertical format.

    Category:  Beverages  Products:  Chai, Chang, Chartreuse
                                            verte, Cote de Blaye,
                                            Guarana Fantastica, Ipoh
                                            Coffee, Lakkalikoori,
                                            Laughing Lumberjack
                                            Lager, Outback Lager,
                                            Rhonbrau Klosterbier,
                                            Sasquatch Ale, Steeleye
                                            Stout
    
           Category:  Condiments  Products: Aniseed Syrup, Chef
                                            Anton's Cajun Seasoning,
                                            Chef Anton's Gumbo Mix,
                                            Genen Shouyu, Grandma's
                                            Boysenberry Spread,
                                            Gula Malacca, Louisiana
                                            Fiery Hot Pepper Sauce,
                                            Louisiana Hot Spiced
                                            Okra, Northwoods
                                            Cranberry Sauce,
                                            Original Frankfurter
                                            grune Sobe, Sirop
                                            d'erable, Vegie-spread