The information in this article applies to:
- Microsoft Access versions 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes a technique that you can use to concatenate
information from multiple records based on the many side of a relationship
and to format the results into a single line separated by commas on a
report.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access version 2.0. For more information about Access Basic, please refer
to the "Building Applications" manual.
MORE INFORMATION
CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb (or NWIND.MDB in version 2.0). You may want to
back up the Northwind.mdb (or NWIND.MDB) file and perform these steps
on a copy of the database.
- 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
- Create the following report:
Report: rptCategoriesProducts
-----------------------------------
Caption: Products by Category
RecordSource: qryCategoriesProducts
- 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
- Name the CategoryName header section grpHeaderCategoryID and set the
header section's Height property to zero (0).
- Place the ProductName field in the report's detail section and set the
detail section's Visible property to No.
- Place the CategoryName field in the report's Category Name footer
section.
- 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.
- 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
- 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
- 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
- Close and save the report.
- 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
|