Need Help: Combining subreport results into single string

[elvindeath]

Hi there. I've created a database for a document repository. Document
collections are being stored in boxes, and each box may contain
documents that fit a number of different "categories" or "keywords".
For instance, a box may contain documents with keywords of Purchasing,
Sales, and Inventory.

The database structure has a main table called tblDocuments, a table
listing the keywords called tblCategories and another table called
tblDocumentsCategories to crossreference the different keywords which
apply to the individual records. Obviously, each entry in tblDocuments
may have a number of corresponding entries in the
tblDocumentsCategories table, depending on how many keywords are
assigned to describe that specific collection.

My question is this:

I have created a master report, which shows details for each of the
document collections. In this report, I have created a "sub report",
which takes data from a seperate query to show all of the categories
that have been assigned to that specific collection. However, the
subreport returns those results in a list with each category on a
sepeate line.

Is there some way to get the subreport to return the results in a
single "sentence" or text box. So that instead of the report listing
the categories in the above example as:

Purchasing
Sales
Inventory

It would return the results in a single, expanding-to-fit text box as
"Purchasing, Sales, Inventory" ?

I know there's got to be some solution I've overlooked, but I can't
seem to find a direct answer on Google and, truth be told, after
several years of atrophy, both my Access skills and my VB skills are
most charitably described as being on part with a moderately
experienced user.

Thanks in advance for any help !

 

[Allen Browne]

Simplest solution might be a VBA function that returns a string listing the
related records. You can then replace the subreport with a text box.

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

 

[elvindeath]

Thanks, Allen. I'll check this out and see if I can get it working.
It seems pretty straightforward, and exactly what I need.