How to use name concatenation in multi-record report

[Sean O'Halloran]

I've searched for two hours here but can't find the answer on how to do this:

My problem: Trainees get certificates when they complete a class. We print out certificates that say:

"Joe Schmegacki attended 'How to Swim with Sharks' with the trainers Capt. Ahab, Mr. Jonah, Dr. Cousteau."
I'm attempting to get the trainers' names to display and print as if they are part of the paragraph, and I'm getting close: In my forum search I came across this link to a Microsoft article, which shows how to do that for ONE instance of the report:

http://support.microsoft.com/default...b;en-us;141624

I followed the instructions and created the report (adjusting the code to fit my field names), and that subreport works for the first trainee certificate (the mainform). The second trainee certificate gets 'Capt. Ahab, Mr. Jonah, Dr. Cousteau, Capt. Ahab, Mr. Jonah, Dr. Cousteau', and so on, doubling on each new certificate.

In teh code below ALLTRAINERS is and unbound text box, FULLNAME is bound to field which concatenates the trainer's title and first and last names.

Here's the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Local Error GoTo Detail_Format_Err
If Not FirstPass Then
Me!AllTrainers = Me![FullName]
FirstPass = True
Else
Me!AllTrainers = Me!AllTrainers & ", " & Me![FullName]

End If
End Sub


Could somone please help me with advice on how to amend the code, or point me in the right direction? Thanks in advance for your help,

Sean

 

[Mile-O]

Me!AllTrainers = Me!AllTrainers & ", " & Me![FullName]

This line here seems to be your problem as it continually concatenates your field. You will need to reset it on each instance of the report.

 

[Sean O'Halloran]

Dear Mile-O,

Thank you for your reply. I don't know how to do that; I'll start researching. By the way, am I covering well-trod ground or is this something that, when I get it working, is worth posting? I don't want to waste forum space / time, but I would like to share if others might find it useful, if only to "pay-forward" for all the assistance I received here.

Thanks again,
Sean

 

[Mile-O]

To be honest, I've been away from Access for so long (and never really used reports when I did use it) that I'm not au fait with the Report events, like On Detail Format.

But when it comes to solutions, I think it's always best to post them no matter how trivial they may seem. You never know, this thread may be the access point by which someone comes to this site searching on such a problem and it can be frustrating to read when there is no answer at the end.

 

[Sean O'Halloran]

Any bright ideas?

I hope someone will post some code, because all the VBA I know would fit inside a thimble...my search for 'reset' 'requery' 'code reset', 'report instance' 'code reset', etc. Has Not Brought Me The Enlightenment I Seek. If anyone has any ideas I would be most grateful.

Sean

 

[Sean O'Halloran]

Message to Self: It helps if you RTFM !!

To anyone who reads this post: The way to build this report function is clearly presented in the Microsoft KB article whose link is shown in the thread-starter. I simply skipped a step in creating my report subform (the subform to display the trainers' names): I neglected to build the grpHeader event as described in step 9 of the KB article.

Here's the code for the step I missed:

Sub grpHeaderCategoryID_Format (Cancel As Integer, _FormatCount As Integer)
Me!AllTrainers = Null
FirstPass = False
End Sub

If you read VB as badly as I do, what this bit of code does is reset the code in the Detail section "OnFormat" event (the code I cited above, and that Mile-O-Phile correctly ID'd as needing to be reset). It sets the "AllTrainers" text box back to null so that each new instance of the report gets just the one correct set of trainer names.

Which I should have realized when I read the instructions the first time!

Sorry for wasting your time, and I hope this helps.

Sean