Concatenated fields for the Enclosure line in a merge?

[bertrandr]

Hi, Everyone.
This is my third post. Thank you all for helping in the past. I have a new question I was hoping you could help me with.

I am developing a database for a small non-profit. We generate letters using our Access database, and I am having trouble with the Enclosure line of the merge word doc template.

Parent record Fields:
*LetterID
DateofLetter
LastName
FirstName
Address fields
LetterBody
Signature

Child Fields:
*LetterID
*HandoutCode

Most letters we send out have more than one enclosure (handout). The enclosures are usually different types of free informational handouts. Currently the fields for the content of the letter are propagated by the parent record, and then I enter a new child row for each different enclosure. Each enclosure is entered as a different child because we need a-year-end-count of all the different enclosures we send out for each individual and for the organization as a whole. Also, I use a lookup list so it is easier to remember all the enclosures we send and their appropriate abbreviations. Otherwise, I would just use an Enclosure memo Field and have the users just type out each Enclosure abbreviation.

How do I get it so that I can still keep a count of the enclosures that we send out at the end of the year and have the list of all the enclosures show up on the Enclosure line of my merged letter? With the way I am doing it now, I can only get one enclosure per letter I generate.

Any ideas? Is this an update query procedure? The Access Bible 2002 pg 1032 has something that uses a multiselect list box using add and remove buttons, but I'm not sure if this is what I should be doing. Their example uses a Value List and not a table list, and also, and I cannot tell how I would connect the list of selection to a field in a query or a memo field.

Thanks to everyone out there for any direction you can give.

Bertie

 

[OnTheFly]

I am not sure where exactly the data is stored. If it is a multi-select list box on a form you can loop through and get the selected items concatinating them into a single string item.

If the data is stored in a table then you could do the same thing, looping through the records and creating a string value of the items.



Hope this helps.

OnTheFly

 

[JonFer]

I'm not an expert but I know you can use multiple records on one document in the mail merge using a "next record" merge control or something like that. You can also put If logic for determining whether to display data. I remember doing this once several years ago and had to generate the same number of records per letter (some rows were blank for the letter fields) so that I could use the same number of "next record" commands. You may have more control now with mail merge and not have to create additional blank records.

John

http://www.interautomateconsulting.com

 

[dhookom]

I have done this in the past using the generic concatenate function in faq701-4233. There is a sample mdb at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that shows how to use this function. You can concatenate line breaks and/or tabs in the resulting expression.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bertrandr]

Dear Duane,

Your examples were great. Thank you. It worked perfectly.
At first it didn't work because I had forgotten to import your module. After importing it, it worked perfectly. Thanks so much for providing it to everyone.

Bertie